Filter Queries

Found 1091 queries.

Page 47 of 55
  • 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 tables

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 License: MIT (opens in new tab)

#922. Different suffixes of a candidate key column and a referencing foreign key column

system catalog base tables only

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 License: MIT (opens in new tab)

#923. Phone number columns lacking digit validation constraints

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#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 tables

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 License: MIT (opens in new tab)

#925. Base table columns permitting empty strings and strings that consist of only whitespace characters (2)

INFORMATION_SCHEMA+system catalog base tables

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:


  • 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.

Problem detection License: MIT (opens in new tab)

#926. Primary key columns not positioned first

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#927. Data-modifying (UPDATE or DELETE) routines lacking Optimistic Concurrency Control (row-version checking)

INFORMATION_SCHEMA+system catalog base tables

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 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.

Problem detection License: MIT (opens in new tab)

#928. Optional non-foreign columns participating in unique keys

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#929. Optional columns participating in unique keys

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#930. Nullable timestamps missing an infinity default

INFORMATION_SCHEMA only

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 License: MIT (opens in new tab)

#931. Excessive locking with FOR UPDATE in subqueries

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#932. Redundant and unconditional SELECT ... FOR UPDATE preceding UPDATE or DELETE on the same table

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#933. Function-based index usage mismatch in derived tables

INFORMATION_SCHEMA+system catalog base 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 License: MIT (opens in new tab)

#934. Function-based index usage mismatch in user-defined routines

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#935. Routines that use old syntax for limiting rows

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#936. Dynamic values in primary error messages

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#937. Invalid or generic error codes in exceptions

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#938. Multiple columns in exception COLUMN clause

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#939. Missing dynamic values in exception details

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#940. Missing error context in exceptions

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)
# 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:

  • 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.
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 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.

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