| # | Name | Goal | Type | Data source | Last update ▼ | License | |
|---|---|---|---|---|---|---|---|
| 1 | Surrogate keys using non-standard SERIAL pseudo-type | This query identifies surrogate key columns defined using the legacy, PostgreSQL-specific SERIAL (or BIGSERIAL) pseudo-type. While functional, this notation is not part of the ISO SQL standard. The recommended best practice in modern PostgreSQL versions is to utilize GENERATED AS IDENTITY columns. Identity columns are standard-compliant and offer superior management of underlying sequences and permissions compared to the older SERIAL implementation. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2026-01-24 20:43 | MIT License | View |
| 2 | Redundant sequence usage privileges (missing table insert rights) | This query identifies a logical mismatch in access control lists (ACLs). It flags cases where a role is granted USAGE privilege on a sequence (allowing the generation of values via nextval) but lacks the INSERT privilege on the table associated with that sequence. Since the primary purpose of such a sequence is to generate surrogate keys for new rows, possessing the right to generate IDs without the right to insert rows renders the sequence privilege functionally useless. This violates the principle of least privilege and should be revoked to minimize the attack surface. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2026-01-21 11:50 | MIT License | View |
| 3 | Privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers | This query performs a comprehensive security audit of access control lists (ACLs) across a wide range of database objects, including the database itself, schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers. It retrieves privileges granted to any principal other than the object's owner or the systemic postgres superuser. The objective is to identify and validate "third-party" access rights, ensuring compliance with the principle of least privilege and preventing unauthorized access accumulation. | General | system catalog base tables only | 2026-01-21 10:52 | MIT License | View |
| 4 | Redundant CHECK constraints (logical subsumption or equivalence) (empty strings) | This query identifies superfluous CHECK constraints by detecting logical subsumption. It targets columns where a generic validation ensuring the trimmed string is not empty (e.g., trim(column) <> '') is rendered redundant by a more specific constraint that enforces a minimum length on the trimmed string (e.g., char_length(trim(column)) > 0). Since a string with a positive length is inherently not empty, the generic check adds no functional value and should be removed to simplify the schema. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2026-01-21 09:43 | MIT License | View |
| 5 | Redundant CHECK constraints (logical subsumption or equivalence) (empty strings and strings that consist of whitespace characters) (2) | This query identifies superfluous CHECK constraints by detecting logical subsumption. It targets columns where a general non-blankness constraint is made redundant by a more specific, format-validating constraint. For instance, if an e_mail column is validated by a format constraint from Set1 (e.g., e_mail LIKE '%@%'), that constraint implicitly ensures the string is not blank. Therefore, any co-existing constraint from Set2 (e.g., e_mail !~ '^[[:space:]]*$') is logically unnecessary and can be removed to reduce schema complexity. Example. Set1: {e_mail~'[[:alnum:]@]+'; position('@' in e_mail)>0; e_mail LIKE '%@%'} Set2: {e_mail~'\S'; e_mail!~'^[[:space:]]*$'; e_mail!~'^\s*$'} If column e_mail has a constraint from Set1, then it does not need a constraint from Set2. |
Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2026-01-21 09:39 | MIT License | View |
| 6 | Too generic names (columns) (aggregate view)(2) | This query calculates the total cardinality of semantically weak column identifiers across the entire schema, encompassing both base tables and views. It applies a uniform definition of "generic" (e.g., flagging identifiers like id, type, date) regardless of the underlying object type. By treating table and view columns equivalently, the query provides a holistic metric of naming ambiguity, quantifying the overall prevalence of non-descriptive attributes within the database's public interface. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2026-01-21 09:17 | MIT License | View |
| 7 | Too generic names (columns) (aggregate view) | This query generates a frequency distribution of semantically weak column identifiers within the schema. It aggregates the occurrences of generic names (e.g., id, type, data) to quantify their prevalence across the database. This statistical view serves as a prioritization tool for refactoring, highlighting the most ubiquitous violations of naming conventions that contribute to schema ambiguity. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2026-01-21 09:15 | MIT License | View |
| 8 | Too generic names (columns) | This query audits the schema for semantically weak column identifiers. It identifies columns named with generic nouns (e.g., data, info, value) or context-free technical terms (e.g., id, type, code, date, fk, pk). Such names violate the principle of self-documenting schema design, as they fail to describe the specific domain attribute being stored. To prevent ambiguity—especially in complex joins—column names should carry sufficient semantic context (e.g., product_type instead of type, creation_date instead of date). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2026-01-21 09:11 | MIT License | View |
| 9 | Inadequate length constraints on address-related columns | This query identifies base table columns designated for storing address components (e.g., ip addres e-mail, telephone number, physical location) that lack appropriate length constraints reflecting real-world data requirements. It operates on a heuristic basis, targeting columns whose identifiers imply address data (e.g., names containing "addr" or "mail") but whose definitions fail to account for standard maximum lengths. This includes both insufficient allocation (truncation risk) and unbounded allocation (data quality risk). Ensuring these fields are sized according to domain standards is crucial for data integrity and usability. | Problem detection | INFORMATION_SCHEMA only | 2026-01-21 09:04 | MIT License | View |
| 10 | Coupling of distinct entity lifecycles via shared state classifiers | This query identifies a potential domain modeling flaw where multiple distinct entity tables reference a single, shared state classifier table. According to robust design principles, each main entity type should define its own independent state machine and lifecycle. Sharing a classifier creates undesirable coupling; even if the state vocabularies (e.g., 'Active', 'Inactive') appear identical currently, the business logic for distinct entities is likely to diverge over time. Furthermore, reliance on a universal state table often indicates an under-analyzed domain model utilizing overly generic state transitions. | Problem detection | system catalog base tables only | 2026-01-19 17:36 | MIT License | View |
| 11 | The reference to a database operation is missing from a comment | This query audits the metadata of user-defined routines to enforce traceability between the implementation and the design specifications. It identifies routines whose comments lack a standardized reference to the specific database operation contract they implement. The query checks for the absence of a required identifier pattern, typically formatted as OP followed by a number (e.g., OP1, OP12). Enforcing this standard ensures that every routine can be mapped back to its originating requirement or business rule. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2026-01-19 16:18 | MIT License | View |
| 12 | Triggers with SELECT (i.e., probably check data based on another table) | This query identifies trigger functions intended for constraint enforcement that are susceptible to concurrency anomalies due to PostgreSQL's Multi-Version Concurrency Control (MVCC) model. Since read operations (SELECT) do not block write operations, a trigger that validates cross-row constraints without acquiring explicit locks (e.g., LOCK TABLE or SELECT ... FOR UPDATE) involves a race condition. The query detects triggers that query auxiliary data. | General | INFORMATION_SCHEMA+system catalog base tables | 2026-01-19 15:19 | MIT License | View |
| 13 | Unique index definition instead of a key declaration | This query identifies semantic abstraction violations where data uniqueness is enforced via low-level CREATE UNIQUE INDEX statements instead of declarative PRIMARY KEY, UNIQUE, or EXCLUDE constraints. It specifically targets "plain" unique indexes (excluding partial or expression-based indexes) that are functionally identical to standard constraints. According to the ANSI-SPARC architecture, indexes belong to the internal (physical) schema, while constraints belong to the conceptual schema. Therefore, defining business rules using high-level constraint syntax is preferred for semantic clarity and architectural correctness. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2026-01-19 14:37 | MIT License | View |
| 14 | Unbounded textual columns for non-descriptive attributes | This query identifies base table columns defined as unbounded TEXT or VARCHAR (without a length specifier) that lack any corresponding CHECK constraint to restrict value length. It explicitly excludes foreign key columns and columns heuristically identified as descriptive fields (e.g., names containing "comment", "description", "note"), where arbitrary length is typically acceptable. For structured attributes (such as names, codes, or identifiers), relying on unbounded types without constraints is a design risk, potentially allowing excessive data payload, complicating index usage, and violating domain constraints. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2026-01-19 14:10 | MIT License | View |
| 15 | Updatable views with WHERE clause that do not have WITH CHECK OPTION constraint | This query identifies automatically updatable views that define a row restriction (via a WHERE clause) but lack the WITH CHECK OPTION constraint. In the absence of this constraint, it is possible to perform INSERT or UPDATE operations through the view that result in rows satisfying the base table constraints but failing the view's inclusion criteria. This leads to "phantom updates," where the modified data is committed to the database but immediately disappears from the view's scope. Enforcing WITH CHECK OPTION ensures that all modifications performed through the view respect its defining predicate. | Problem detection | INFORMATION_SCHEMA only | 2026-01-19 14:02 | MIT License | View |
| 16 | Find useless coalesce, concat, or concat_ws calls with only one argument | This query identifies superfluous function calls within routines and views, specifically targeting invocations of coalesce(), concat(), or concat_ws() that are supplied with only a single argument. These functions are variadic and designed to operate on multiple values (e.g., returning the first non-null value or joining strings). When called with a single argument, they function as an identity operation, returning the input unchanged. This pattern indicates either a coding error (missing arguments) or redundant logic that should be removed to simplify the expression. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-30 13:51 | MIT License | View |
| 17 | Find columns that require new lookup tables (based on names and field sizes) | This query detects data attributes that function as implicit classifiers but lack a corresponding reference entity. It targets textual columns that are not currently constrained (no PK, UK, or FK) but exhibit characteristics of coded data: they either have a very short length (≤ 3 characters) or possess identifiers typical of classifiers (e.g., 'status', 'type'). To reduce false positives, it excludes obvious free-text fields (names, comments) and verifies that no table with a similar name currently exists. This suggests the need to extract these attributes into a new dedicated reference table to enforce domain integrity. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-30 09:14 | MIT License | View |
| 18 | Textual code columns lacking specific pattern validation | This query identifies semantic validation gaps in textual columns intended to store structured codes. It targets non-foreign key columns whose identifiers imply a specific format (e.g., containing the word "code"), but which lack adequate constraints to enforce that format. Specifically, it flags columns that have either no CHECK constraints at all, or only trivial constraints that prohibit empty/whitespace strings. Since "codes" typically adhere to a strict pattern (e.g., fixed length, specific character set), relying solely on a non-empty check is considered insufficient for data integrity. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-29 13:14 | MIT License | View |
| 19 | Permitting in a column only empty strings and strings that consist of whitespace characters | This query identifies logically flawed CHECK constraints on table columns. It specifically searches for constraints that are defined in such a way that they only permit the insertion of an empty string or strings composed entirely of whitespace characters. This behavior is invariably a bug, likely a typo in the constraint's logic (e.g., using ~ instead of !~), which effectively renders the column unusable for storing meaningful data. | Problem detection | INFORMATION_SCHEMA only | 2025-12-29 12:03 | MIT License | View |
| 20 | Identity columns configured as GENERATED BY DEFAULT | This query identifies identity columns defined with the GENERATED BY DEFAULT clause. Unlike GENERATED ALWAYS, this configuration permits manual insertion of values into the identity column without explicit overrides. This flexibility creates a significant risk of sequence desynchronization: if a user manually inserts a key value that exceeds the current sequence state, the sequence will eventually generate a colliding value. This results in runtime unique constraint violations (primary key conflicts) that are difficult to predict and resolve. The preferred pattern for surrogate keys is typically GENERATED ALWAYS to enforce system-controlled uniqueness. | Problem detection | INFORMATION_SCHEMA only | 2025-12-26 14:45 | MIT License | View |