Filter Queries
- All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
- Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets, it shouldn't be a problem in case of the relatively small amount of data in the system catalog of a database.
#921. Multi-column CHECK constraints with unnecessary NULL checks
INFORMATION_SCHEMA+system catalog base tablesThis query identifies multiple-column CHECK constraints that explicitly check for NULL values. In general, constraint definitions should be kept as simple as possible. Because a NULL value causes a logical condition to evaluate to UNKNOWN—and CHECK constraints inherently allow rows that evaluate to either TRUE or UNKNOWN—there is usually no need to explicitly allow missing values. For instance, instead of writing CHECK (last_update_time IS NULL OR last_update_time >= creation_time), the constraint should simply be written as CHECK (last_update_time >= creation_time). However, a valid exception exists for implication rules (P ⇒ Q), which can be rewritten as NOT (P) OR Q. For example, the constraint CHECK (NOT (product IS NOT NULL) OR service IS NULL) properly enforces the rule that if a product is present, the service must be NULL. Such logical constructs are entirely appropriate.
#922. Different suffixes of a candidate key column and a referencing foreign key column
system catalog base tables onlyThis query identifies naming inconsistencies in foreign key relationships by comparing the suffixes of foreign key columns against their referenced candidate key columns. It flags pairs where the suffixes diverge (e.g., referencing user_id via a column named user_code or user_ref). Such discrepancies prevent the use of the simplified ANSI SQL USING syntax for joins, forcing the use of explicit ON clauses. The goal is to enforce a standardized suffix convention (typically _id) across the schema to improve maintainability and query readability.
#923. Phone number columns lacking digit validation constraints
INFORMATION_SCHEMA+system catalog base tablesThis query identifies non-foreign key base table columns intended for telephone number storage that lack essential data validation. It targets columns whose names imply phone data (e.g., containing "phone", "tel") but which have no associated simple CHECK constraint validating the presence of numeric digits. Without such a constraint (e.g., a regex check for [0-9]), the column allows for invalid entries such as purely alphabetic strings or email addresses, compromising data integrity. The query assumes that a valid phone number must minimally contain digits.
#924. Base table columns that lack any simple CHECK, i.e, permit empty strings and strings that consist of only whitespace characters
INFORMATION_SCHEMA+system catalog base tablesThis query identifies non-foreign key columns of base tables with a textual data type that lack any simple (single-column) CHECK constraint. The absence of such constraints indicates a complete lack of column-level validation, creating a risk of low-quality data ingress, including the implicit allowance of empty or whitespace-only strings.
#925. Base table columns permitting empty strings and strings that consist of only whitespace characters (2)
INFORMATION_SCHEMA+system catalog base tablesThis query identifies non-foreign key columns with a textual data type that lack essential validation. It specifically targets columns that are missing both of the following fundamental checks:
- A constraint to prohibit the insertion of empty or whitespace-only strings.
- A constraint to enforce a character set or format policy (e.g., via a regular expression).
The absence of such comprehensive validation increases the risk of poor data quality and potential application-level bugs.
#926. Primary key columns not positioned first
INFORMATION_SCHEMA+system catalog base tablesThis query identifies non-inherited base tables where the primary key columns are not positioned at the very beginning of the table. Specifically, it flags tables where at least one non-primary key column precedes a primary key column in the ordinal position. Placing primary key columns first is a recognized best practice that significantly improves schema readability and comprehension. Tables created via inheritance are appropriately excluded from this check, as inherited columns automatically precede child-specific columns, which can unavoidably force primary keys out of the initial positions.
#927. Data-modifying (UPDATE or DELETE) routines lacking Optimistic Concurrency Control (row-version checking)
INFORMATION_SCHEMA+system catalog base tablesThis query identifies non-trigger user-defined routines (functions and procedures) that execute UPDATE or DELETE statements without implementing Optimistic Concurrency Control (OCC) via the PostgreSQL xmin system column or a dedicated row-version column (such as rea_versioon, versioon, version, or row_version).
In interactive multi-user applications, a user typically reads a record onto a screen, reviews or edits it (user think-time), and then invokes a database routine to save or delete the record in a new database transaction. Because database locks cannot be held across user think-time, executing an UPDATE or DELETE statement that filters solely by the entity's identifier without verifying that the row's version matches the version originally read by the user exposes the system to the Lost Update anomaly: if another user modifies the record in the meantime, the second transaction will blindly overwrite or delete those intermediate changes without warning.
#928. Optional non-foreign columns participating in unique keys
INFORMATION_SCHEMA+system catalog base tablesThis query identifies optional (nullable) base table columns that participate in a UNIQUE constraint or index, but do not participate in a FOREIGN KEY constraint. In database design, tables use candidate keys to uniquely identify rows; one acts as the primary key, while the others serve as alternate keys. To properly enforce a standalone alternate key, all its columns should be mandatory (NOT NULL). Columns involved in foreign keys are excluded from this check, as optional relationships legitimately require nullable foreign keys. The query also exempts constraints that explicitly use the UNIQUE NULLS NOT DISTINCT clause, which safely treats NULL values as equal.
#929. Optional columns participating in unique keys
INFORMATION_SCHEMA+system catalog base tablesThis query identifies optional (nullable) base table columns that participate in a UNIQUE constraint or unique index. In database design, each base table has one or more candidate keys; one is typically selected as the primary key, while the others serve as alternate keys. To properly enforce an alternate key, its underlying UNIQUE constraint should consist entirely of mandatory (NOT NULL) columns, just as primary key columns are. This query highlights columns where this mandatory constraint might be missing, excluding constraints that explicitly use the UNIQUE NULLS NOT DISTINCT clause.
#930. Nullable timestamps missing an infinity default
INFORMATION_SCHEMA onlyThis query identifies optional (nullable) timestamp columns in base tables that lack a default value. It highlights fields that might represent open-ended time periods (such as expiration or end dates). In such cases, it is often a better practice to assign the special value 'infinity' as the default, rather than relying on NULL values.
#931. Excessive locking with FOR UPDATE in subqueries
INFORMATION_SCHEMA+system catalog base tablesThis query identifies performance and concurrency bottlenecks caused by excessive locking. It flags INSERT, UPDATE, or DELETE statements that utilize subqueries containing the FOR UPDATE clause. Using FOR UPDATE acquires an exclusive lock, which is semantically inappropriate if the rows in the subquery are merely being read for reference or validation rather than being modified. This practice degrades system concurrency by unnecessarily blocking other transactions.
#932. Redundant and unconditional SELECT ... FOR UPDATE preceding UPDATE or DELETE on the same table
INFORMATION_SCHEMA+system catalog base tablesThis query identifies performance and concurrency bottlenecks in user-defined routines caused by executing a standalone SELECT ... FOR UPDATE or PERFORM ... FOR UPDATE statement on a table prior to modifying the exact same table with an UPDATE or DELETE statement. In PostgreSQL, UPDATE and DELETE statements automatically acquire an exclusive row-level lock on target rows that satisfy the WHERE predicate. Executing a separate, preceding SELECT/PERFORM ... FOR UPDATE on the same table (without an ORDER BY clause for deterministic multi-row deadlock prevention) is redundant—incurring an unnecessary extra scan and lock operation—and degrades concurrency when the initial lock predicate is less restrictive than the subsequent modification (for instance, locking a row solely by its primary key before verifying its status or optimistic lock version). If the subsequent UPDATE or DELETE affects zero rows due to a version mismatch or invalid state, the transaction still retains the exclusive lock acquired by the preliminary query, unnecessarily blocking concurrent transactions.
#933. Function-based index usage mismatch in derived tables
INFORMATION_SCHEMA+system catalog base tablesThis query identifies performance inefficiencies caused by a semantic mismatch between function-based index definitions and their usage within derived tables (views, materialized views). PostgreSQL's query optimizer generally requires the expression in a query to strictly match the expression defined in the index to trigger an index scan. This query flags instances where a column is indexed using one function (e.g., upper(col)) but accessed in a view using a different function (e.g., lower(col)). In such cases, the optimizer cannot utilize the pre-calculated index, resulting in suboptimal execution plans (typically sequential scans).
#934. Function-based index usage mismatch in user-defined routines
INFORMATION_SCHEMA+system catalog base tablesThis query identifies performance inefficiencies within user-defined routines caused by a mismatch between the logic in the routine and the definition of function-based indexes. PostgreSQL's query optimizer requires the expression in a query to strictly match the expression defined in the index to trigger an index scan. This query flags instances where a column is indexed using one function (e.g., upper(col)) but is accessed in a routine using a different function (e.g., lower(col)). Consequently, the optimizer fails to utilize the pre-calculated index, forcing a costly sequential scan.
#935. Routines that use old syntax for limiting rows
INFORMATION_SCHEMA+system catalog base tablesThis query identifies PL/pgSQL and SQL routines with no SQL-standard bodies that use the non-standard LIMIT clause for row limitation. It flags these routines because the official, cross-platform SQL standard specifies FETCH FIRST n ROWS ONLY for this purpose. Adhering to the standard improves code portability and maintainability. To ensure relevance, the query intelligently excludes routines that are part of installed extensions, focusing only on user-defined code.
#936. Dynamic values in primary error messages
INFORMATION_SCHEMA+system catalog base tablesThis query identifies PL/pgSQL routines that invoke RAISE EXCEPTION and embed dynamic values directly into the primary error message string. This is considered an anti-pattern because it generates a unique error string for every failure, which complicates log aggregation and client-side error parsing. The primary error message should always be static and predictable.
#937. Invalid or generic error codes in exceptions
INFORMATION_SCHEMA+system catalog base tablesThis query identifies PL/pgSQL routines that invoke RAISE EXCEPTION and have malformed, generic, or improperly classified ERRCODE values in RAISE EXCEPTION statements. It acts as a linter for PostgreSQL error handling best practices. Specifically, it flags generic codes (like P0001 or raise_exception), the misuse of warning/info classes (00-02) as breaking exceptions, and the hijacking of reserved standard error classes. It also ensures that custom error codes follow the correct 5-character format and start with a digit between 5 and 9, thereby preventing conflicts with built-in PostgreSQL errors.
#938. Multiple columns in exception COLUMN clause
INFORMATION_SCHEMA+system catalog base tablesThis query identifies PL/pgSQL routines that invoke RAISE EXCEPTION and specify multiple columns within the COLUMN error context clause. This is an anti-pattern because PostgreSQL's column diagnostic field is designed to hold a single column identifier. Providing a list of columns in this field breaks standard error parsing, making it difficult for client applications to map the error to a specific user interface field.
#939. Missing dynamic values in exception details
INFORMATION_SCHEMA+system catalog base tablesThis query identifies PL/pgSQL routines that invoke RAISE EXCEPTION but fail to include dynamic values (such as the problematic data itself) in the DETAIL clause. Providing only static text in error details is an anti-pattern, as it makes debugging significantly harder. Developers and client applications need to see the actual rejected values to quickly understand and fix the underlying issue.
#940. Missing error context in exceptions
INFORMATION_SCHEMA+system catalog base tablesThis query identifies PL/pgSQL routines that use the RAISE EXCEPTION statement but fail to provide additional error context, such as ERRCODE, DETAIL, HINT, SCHEMA, TABLE, or CONSTRAINT. Defining these supplementary clauses is a best practice in PL/pgSQL, as it provides developers and client applications with essential diagnostic information for robust error handling.
| # | Name(activate to sort ascending) | Goal(sorted ascending, activate to sort descending) | Type(activate to sort ascending) | Data source(activate to sort ascending) | Last update(activate to sort ascending) | License | Actions |
|---|---|---|---|---|---|---|---|
| 921 | Multi-column CHECK constraints with unnecessary NULL checks | This query identifies multiple-column CHECK constraints that explicitly check for NULL values. In general, constraint definitions should be kept as simple as possible. Because a NULL value causes a logical condition to evaluate to UNKNOWN—and CHECK constraints inherently allow rows that evaluate to either TRUE or UNKNOWN—there is usually no need to explicitly allow missing values. For instance, instead of writing CHECK (last_update_time IS NULL OR last_update_time >= creation_time), the constraint should simply be written as CHECK (last_update_time >= creation_time). However, a valid exception exists for implication rules (P ⇒ Q), which can be rewritten as NOT (P) OR Q. For example, the constraint CHECK (NOT (product IS NOT NULL) OR service IS NULL) properly enforces the rule that if a product is present, the service must be NULL. Such logical constructs are entirely appropriate. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 922 | Different suffixes of a candidate key column and a referencing foreign key column | This query identifies naming inconsistencies in foreign key relationships by comparing the suffixes of foreign key columns against their referenced candidate key columns. It flags pairs where the suffixes diverge (e.g., referencing user_id via a column named user_code or user_ref). Such discrepancies prevent the use of the simplified ANSI SQL USING syntax for joins, forcing the use of explicit ON clauses. The goal is to enforce a standardized suffix convention (typically _id) across the schema to improve maintainability and query readability. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 923 | Phone number columns lacking digit validation constraints | This query identifies non-foreign key base table columns intended for telephone number storage that lack essential data validation. It targets columns whose names imply phone data (e.g., containing "phone", "tel") but which have no associated simple CHECK constraint validating the presence of numeric digits. Without such a constraint (e.g., a regex check for [0-9]), the column allows for invalid entries such as purely alphabetic strings or email addresses, compromising data integrity. The query assumes that a valid phone number must minimally contain digits. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 924 | Base table columns that lack any simple CHECK, i.e, permit empty strings and strings that consist of only whitespace characters | This query identifies non-foreign key columns of base tables with a textual data type that lack any simple (single-column) CHECK constraint. The absence of such constraints indicates a complete lack of column-level validation, creating a risk of low-quality data ingress, including the implicit allowance of empty or whitespace-only strings. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 925 | Base table columns permitting empty strings and strings that consist of only whitespace characters (2) | This query identifies non-foreign key columns with a textual data type that lack essential validation. It specifically targets columns that are missing both of the following fundamental checks:
The absence of such comprehensive validation increases the risk of poor data quality and potential application-level bugs. |
Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 926 | Primary key columns not positioned first | This query identifies non-inherited base tables where the primary key columns are not positioned at the very beginning of the table. Specifically, it flags tables where at least one non-primary key column precedes a primary key column in the ordinal position. Placing primary key columns first is a recognized best practice that significantly improves schema readability and comprehension. Tables created via inheritance are appropriately excluded from this check, as inherited columns automatically precede child-specific columns, which can unavoidably force primary keys out of the initial positions. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 927 | Data-modifying (UPDATE or DELETE) routines lacking Optimistic Concurrency Control (row-version checking) | This query identifies non-trigger user-defined routines (functions and procedures) that execute UPDATE or DELETE statements without implementing Optimistic Concurrency Control (OCC) via the PostgreSQL xmin system column or a dedicated row-version column (such as rea_versioon, versioon, version, or row_version). In interactive multi-user applications, a user typically reads a record onto a screen, reviews or edits it (user think-time), and then invokes a database routine to save or delete the record in a new database transaction. Because database locks cannot be held across user think-time, executing an |
Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 928 | Optional non-foreign columns participating in unique keys | This query identifies optional (nullable) base table columns that participate in a UNIQUE constraint or index, but do not participate in a FOREIGN KEY constraint. In database design, tables use candidate keys to uniquely identify rows; one acts as the primary key, while the others serve as alternate keys. To properly enforce a standalone alternate key, all its columns should be mandatory (NOT NULL). Columns involved in foreign keys are excluded from this check, as optional relationships legitimately require nullable foreign keys. The query also exempts constraints that explicitly use the UNIQUE NULLS NOT DISTINCT clause, which safely treats NULL values as equal. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 929 | Optional columns participating in unique keys | This query identifies optional (nullable) base table columns that participate in a UNIQUE constraint or unique index. In database design, each base table has one or more candidate keys; one is typically selected as the primary key, while the others serve as alternate keys. To properly enforce an alternate key, its underlying UNIQUE constraint should consist entirely of mandatory (NOT NULL) columns, just as primary key columns are. This query highlights columns where this mandatory constraint might be missing, excluding constraints that explicitly use the UNIQUE NULLS NOT DISTINCT clause. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 930 | Nullable timestamps missing an infinity default | This query identifies optional (nullable) timestamp columns in base tables that lack a default value. It highlights fields that might represent open-ended time periods (such as expiration or end dates). In such cases, it is often a better practice to assign the special value 'infinity' as the default, rather than relying on NULL values. | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 931 | Excessive locking with FOR UPDATE in subqueries | This query identifies performance and concurrency bottlenecks caused by excessive locking. It flags INSERT, UPDATE, or DELETE statements that utilize subqueries containing the FOR UPDATE clause. Using FOR UPDATE acquires an exclusive lock, which is semantically inappropriate if the rows in the subquery are merely being read for reference or validation rather than being modified. This practice degrades system concurrency by unnecessarily blocking other transactions. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 932 | Redundant and unconditional SELECT ... FOR UPDATE preceding UPDATE or DELETE on the same table | This query identifies performance and concurrency bottlenecks in user-defined routines caused by executing a standalone SELECT ... FOR UPDATE or PERFORM ... FOR UPDATE statement on a table prior to modifying the exact same table with an UPDATE or DELETE statement. In PostgreSQL, UPDATE and DELETE statements automatically acquire an exclusive row-level lock on target rows that satisfy the WHERE predicate. Executing a separate, preceding SELECT/PERFORM ... FOR UPDATE on the same table (without an ORDER BY clause for deterministic multi-row deadlock prevention) is redundant—incurring an unnecessary extra scan and lock operation—and degrades concurrency when the initial lock predicate is less restrictive than the subsequent modification (for instance, locking a row solely by its primary key before verifying its status or optimistic lock version). If the subsequent UPDATE or DELETE affects zero rows due to a version mismatch or invalid state, the transaction still retains the exclusive lock acquired by the preliminary query, unnecessarily blocking concurrent transactions. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 933 | Function-based index usage mismatch in derived tables | This query identifies performance inefficiencies caused by a semantic mismatch between function-based index definitions and their usage within derived tables (views, materialized views). PostgreSQL's query optimizer generally requires the expression in a query to strictly match the expression defined in the index to trigger an index scan. This query flags instances where a column is indexed using one function (e.g., upper(col)) but accessed in a view using a different function (e.g., lower(col)). In such cases, the optimizer cannot utilize the pre-calculated index, resulting in suboptimal execution plans (typically sequential scans). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 934 | Function-based index usage mismatch in user-defined routines | This query identifies performance inefficiencies within user-defined routines caused by a mismatch between the logic in the routine and the definition of function-based indexes. PostgreSQL's query optimizer requires the expression in a query to strictly match the expression defined in the index to trigger an index scan. This query flags instances where a column is indexed using one function (e.g., upper(col)) but is accessed in a routine using a different function (e.g., lower(col)). Consequently, the optimizer fails to utilize the pre-calculated index, forcing a costly sequential scan. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 935 | Routines that use old syntax for limiting rows | This query identifies PL/pgSQL and SQL routines with no SQL-standard bodies that use the non-standard LIMIT clause for row limitation. It flags these routines because the official, cross-platform SQL standard specifies FETCH FIRST n ROWS ONLY for this purpose. Adhering to the standard improves code portability and maintainability. To ensure relevance, the query intelligently excludes routines that are part of installed extensions, focusing only on user-defined code. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 936 | Dynamic values in primary error messages | This query identifies PL/pgSQL routines that invoke RAISE EXCEPTION and embed dynamic values directly into the primary error message string. This is considered an anti-pattern because it generates a unique error string for every failure, which complicates log aggregation and client-side error parsing. The primary error message should always be static and predictable. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 937 | Invalid or generic error codes in exceptions | This query identifies PL/pgSQL routines that invoke RAISE EXCEPTION and have malformed, generic, or improperly classified ERRCODE values in RAISE EXCEPTION statements. It acts as a linter for PostgreSQL error handling best practices. Specifically, it flags generic codes (like P0001 or raise_exception), the misuse of warning/info classes (00-02) as breaking exceptions, and the hijacking of reserved standard error classes. It also ensures that custom error codes follow the correct 5-character format and start with a digit between 5 and 9, thereby preventing conflicts with built-in PostgreSQL errors. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 938 | Multiple columns in exception COLUMN clause | This query identifies PL/pgSQL routines that invoke RAISE EXCEPTION and specify multiple columns within the COLUMN error context clause. This is an anti-pattern because PostgreSQL's column diagnostic field is designed to hold a single column identifier. Providing a list of columns in this field breaks standard error parsing, making it difficult for client applications to map the error to a specific user interface field. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 939 | Missing dynamic values in exception details | This query identifies PL/pgSQL routines that invoke RAISE EXCEPTION but fail to include dynamic values (such as the problematic data itself) in the DETAIL clause. Providing only static text in error details is an anti-pattern, as it makes debugging significantly harder. Developers and client applications need to see the actual rejected values to quickly understand and fix the underlying issue. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 940 | Missing error context in exceptions | This query identifies PL/pgSQL routines that use the RAISE EXCEPTION statement but fail to provide additional error context, such as ERRCODE, DETAIL, HINT, SCHEMA, TABLE, or CONSTRAINT. Defining these supplementary clauses is a best practice in PL/pgSQL, as it provides developers and client applications with essential diagnostic information for robust error handling. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View |