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.
#661. 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.
#662. Privileges to execute routines
INFORMATION_SCHEMA+system catalog base tablesFind privileges to execute routines that have been given to non-superusers. Check as to whether it conforms to the principle of least privilege. Check that users that correspond to applications have all the necessary privileges. Users (applications) should use a database through virtual data layer. Thus, if they need to modify data in the database (in case of table functions read data), then they must execute a routine.
#663. Privileges to use base table columns
INFORMATION_SCHEMA+system catalog base tablesIf you do give privileges to base tables, then these should follow the principle of least privilege - the smallest possible amount of privileges to the smallest possible set of columns
#664. Privileges to use base tables
INFORMATION_SCHEMA+system catalog base tablesUsers (applications) should ideally use a database through virtual data layer and thus not directly use base tables. If there is a need to provide direct access to the base tables, then one should grant access based on the principle of least privilege, i.e., to the minimum possible number of base tables.
#665. Privileges to use views
INFORMATION_SCHEMA+system catalog base tablesFind privileges to use views. Check as to whether it conforms to the principle of least privilege. Check that users that correspond to applications have all the necessary privileges. Users (applications) should use a database through virtual data layer. Thus, if they need to read data from a database, then they should use views.
#666. Procedures cannot have START TRANSACTION and SAVEPOINT
INFORMATION_SCHEMA+system catalog base tablesYou cannot use a START TRANSACTION or a SAVEPOINT statement in a procedure. Procedures appeared in PostgreSQL 11.
#667. PUBLIC has the USAGE privilege of a schema
INFORMATION_SCHEMA+system catalog base tablesFind schemas where PUBLIC has the usage privilege.
#668. Quoted numbers indicating poor type selection
INFORMATION_SCHEMA+system catalog base tablesThis query identifies potential type mismatches by flagging numeric literals enclosed in single quotes. This pattern often triggers unnecessary type casting overhead or indicates a schema design flaw where a textual data type (e.g., VARCHAR) is used to store exclusively numeric data. The recommendation is to either align the data type with the content (e.g., switch to SMALLINT for codes like '1', '2') or ensure the data justifies the textual type by including non-numeric characters. The query explicitly excludes standard SQL error codes found in exception handling routines, as these are syntactically required to be strings.
#669. Range lower bound can be NULL
INFORMATION_SCHEMA+system catalog base tablesThis query identifies columns of base tables that use a RANGE data type but are configured to permit a NULL lower bound. This highlights ranges that can be "unbounded" on their starting side, which may be unintentional and could impact query logic and data constraints.
#670. Range lower bound is not restricted
INFORMATION_SCHEMA+system catalog base tablesThis query finds range columns of base tables that are missing a safety check on their starting value. It looks for columns where the start of the range can be set to any value, without rules to ensure that value makes sense. This helps ensure that all ranges have proper limits defined for their starting points.
#671. Range lower bound restriction does not consider -infinity
INFORMATION_SCHEMA+system catalog base tablesThis query identifies RANGE type columns in base tables that have a NOT NULL constraint on their lower bound and an additional CHECK constraint, but this check does not account for -infinity. This may indicate a "magic number" problem, where a fixed lower limit (e.g., '1900-01-01') is used instead of the more explicit and semantically correct unbounded (-infinity) value.
#672. Range upper bound can be NULL
INFORMATION_SCHEMA+system catalog base tablesThis query identifies columns of base tables that use a RANGE data type but are configured to permit a NULL upper bound. This highlights ranges that can be "unbounded" on their ending side, which may be unintentional and could impact query logic and data constraints.
#673. Range upper bound is not restricted
INFORMATION_SCHEMA+system catalog base tablesThis query finds range columns of base tables that are missing a safety check on their ending value. It looks for columns where the end of the range can be set to any value, without rules to ensure that value makes sense. This helps ensure that all ranges have proper limits defined for their ending points.
#674. Range upper bound restriction dos not consider infinity
INFORMATION_SCHEMA+system catalog base tablesThis query identifies RANGE type columns in base tables that have a NOT NULL constraint on their upper bound and an additional CHECK constraint, but this check does not account for infinity. This may indicate a "magic number" problem, where a fixed upper limit (e.g., '2900-01-01') is used instead of the more explicit and semantically correct unbounded (infinity) value.
#675. Recursive triggers potentially causing infinite loops
INFORMATION_SCHEMA+system catalog base tablesThis query identifies recursive triggers, which occur when a trigger's execution performs an operation (such as an UPDATE on the same table) that immediately causes the same trigger to fire again. This creates a potential infinite loop of execution. While PostgreSQL implements a stack depth limit to detect and terminate such runaway processes to prevent a complete system crash, relying on this fail-safe is poor engineering practice. These triggers consume significant system resources before failure and invariably result in aborted transactions. They should be refactored to avoid self-invocation.
#676. 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.
#677. Redundant CHECK constraints (logical subsumption or equivalence) (empty strings)
INFORMATION_SCHEMA+system catalog base tablesThis 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.
#678. Redundant CHECK constraints (logical subsumption or equivalence) (empty strings and strings that consist of whitespace characters)
INFORMATION_SCHEMA+system catalog base tablesThis query identifies redundant CHECK constraints by detecting cases of logical equivalence or subsumption on a single column. For instance, a constraint like description !~ '^[[:space:]]*$' logically subsumes a less comprehensive constraint such as description <> '', rendering the latter superfluous. Eliminating such duplication is a best practice that improves schema clarity, reduces maintenance overhead, and removes logical noise.
#679. Redundant CHECK constraints (logical subsumption or equivalence) (empty strings and strings that consist of whitespace characters) (2)
INFORMATION_SCHEMA+system catalog base tablesThis 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.
#680. Redundant DO INSTEAD NOTHING rules on naturally non-updatable views
INFORMATION_SCHEMA+system catalog base tablesThis query identifies redundant rewrite rules within the database schema. It targets views that are inherently non-updatable (due to the presence of aggregates, joins, or set operations) but are nevertheless defined with a DO INSTEAD NOTHING rule. Since the PostgreSQL engine cannot perform DML operations on such views natively, the view is effectively read-only by definition. Consequently, the explicit rule serves no functional purpose in preventing data modification and represents superfluous schema metadata.
| # | Name(activate to sort ascending) | Goal(activate to sort ascending) | Type(activate to sort ascending) | Data source(sorted ascending, activate to sort descending) | Last update(activate to sort ascending) | License | Actions |
|---|---|---|---|---|---|---|---|
| 661 | 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 | |
| 662 | Privileges to execute routines | Find privileges to execute routines that have been given to non-superusers. Check as to whether it conforms to the principle of least privilege. Check that users that correspond to applications have all the necessary privileges. Users (applications) should use a database through virtual data layer. Thus, if they need to modify data in the database (in case of table functions read data), then they must execute a routine. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 663 | Privileges to use base table columns | If you do give privileges to base tables, then these should follow the principle of least privilege - the smallest possible amount of privileges to the smallest possible set of columns | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 664 | Privileges to use base tables | Users (applications) should ideally use a database through virtual data layer and thus not directly use base tables. If there is a need to provide direct access to the base tables, then one should grant access based on the principle of least privilege, i.e., to the minimum possible number of base tables. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 665 | Privileges to use views | Find privileges to use views. Check as to whether it conforms to the principle of least privilege. Check that users that correspond to applications have all the necessary privileges. Users (applications) should use a database through virtual data layer. Thus, if they need to read data from a database, then they should use views. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 666 | Procedures cannot have START TRANSACTION and SAVEPOINT | You cannot use a START TRANSACTION or a SAVEPOINT statement in a procedure. Procedures appeared in PostgreSQL 11. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 667 | PUBLIC has the USAGE privilege of a schema | Find schemas where PUBLIC has the usage privilege. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 668 | Quoted numbers indicating poor type selection | This query identifies potential type mismatches by flagging numeric literals enclosed in single quotes. This pattern often triggers unnecessary type casting overhead or indicates a schema design flaw where a textual data type (e.g., VARCHAR) is used to store exclusively numeric data. The recommendation is to either align the data type with the content (e.g., switch to SMALLINT for codes like '1', '2') or ensure the data justifies the textual type by including non-numeric characters. The query explicitly excludes standard SQL error codes found in exception handling routines, as these are syntactically required to be strings. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 669 | Range lower bound can be NULL | This query identifies columns of base tables that use a RANGE data type but are configured to permit a NULL lower bound. This highlights ranges that can be "unbounded" on their starting side, which may be unintentional and could impact query logic and data constraints. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 670 | Range lower bound is not restricted | This query finds range columns of base tables that are missing a safety check on their starting value. It looks for columns where the start of the range can be set to any value, without rules to ensure that value makes sense. This helps ensure that all ranges have proper limits defined for their starting points. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 671 | Range lower bound restriction does not consider -infinity | This query identifies RANGE type columns in base tables that have a NOT NULL constraint on their lower bound and an additional CHECK constraint, but this check does not account for -infinity. This may indicate a "magic number" problem, where a fixed lower limit (e.g., '1900-01-01') is used instead of the more explicit and semantically correct unbounded (-infinity) value. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 672 | Range upper bound can be NULL | This query identifies columns of base tables that use a RANGE data type but are configured to permit a NULL upper bound. This highlights ranges that can be "unbounded" on their ending side, which may be unintentional and could impact query logic and data constraints. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 673 | Range upper bound is not restricted | This query finds range columns of base tables that are missing a safety check on their ending value. It looks for columns where the end of the range can be set to any value, without rules to ensure that value makes sense. This helps ensure that all ranges have proper limits defined for their ending points. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 674 | Range upper bound restriction dos not consider infinity | This query identifies RANGE type columns in base tables that have a NOT NULL constraint on their upper bound and an additional CHECK constraint, but this check does not account for infinity. This may indicate a "magic number" problem, where a fixed upper limit (e.g., '2900-01-01') is used instead of the more explicit and semantically correct unbounded (infinity) value. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 675 | Recursive triggers potentially causing infinite loops | This query identifies recursive triggers, which occur when a trigger's execution performs an operation (such as an UPDATE on the same table) that immediately causes the same trigger to fire again. This creates a potential infinite loop of execution. While PostgreSQL implements a stack depth limit to detect and terminate such runaway processes to prevent a complete system crash, relying on this fail-safe is poor engineering practice. These triggers consume significant system resources before failure and invariably result in aborted transactions. They should be refactored to avoid self-invocation. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 676 | 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 | |
| 677 | 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 | MIT (opens in new tab) | View | |
| 678 | Redundant CHECK constraints (logical subsumption or equivalence) (empty strings and strings that consist of whitespace characters) | This query identifies redundant CHECK constraints by detecting cases of logical equivalence or subsumption on a single column. For instance, a constraint like description !~ '^[[:space:]]*$' logically subsumes a less comprehensive constraint such as description <> '', rendering the latter superfluous. Eliminating such duplication is a best practice that improves schema clarity, reduces maintenance overhead, and removes logical noise. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 679 | 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 | MIT (opens in new tab) | View | |
| 680 | Redundant DO INSTEAD NOTHING rules on naturally non-updatable views | This query identifies redundant rewrite rules within the database schema. It targets views that are inherently non-updatable (due to the presence of aggregates, joins, or set operations) but are nevertheless defined with a DO INSTEAD NOTHING rule. Since the PostgreSQL engine cannot perform DML operations on such views natively, the view is effectively read-only by definition. Consequently, the explicit rule serves no functional purpose in preventing data modification and represents superfluous schema metadata. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View |