Filter Queries

Found 1077 queries.

Page 34 of 54
  • 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 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)

#662. Privileges to execute routines

INFORMATION_SCHEMA+system catalog base tables

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

#663. Privileges to use base table columns

INFORMATION_SCHEMA+system catalog base tables

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

#664. Privileges to use base tables

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

#665. Privileges to use views

INFORMATION_SCHEMA+system catalog base tables

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

#666. Procedures cannot have START TRANSACTION and SAVEPOINT

INFORMATION_SCHEMA+system catalog base tables

You cannot use a START TRANSACTION or a SAVEPOINT statement in a procedure. Procedures appeared in PostgreSQL 11.

Problem detection License: MIT (opens in new tab)

#667. PUBLIC has the USAGE privilege of a schema

INFORMATION_SCHEMA+system catalog base tables

Find schemas where PUBLIC has the usage privilege.

Problem detection License: MIT (opens in new tab)

#668. Quoted numbers indicating poor type selection

INFORMATION_SCHEMA+system catalog base tables

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

#669. Range lower bound can be NULL

INFORMATION_SCHEMA+system catalog base tables

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

#670. Range lower bound is not restricted

INFORMATION_SCHEMA+system catalog base tables

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

#671. Range lower bound restriction does not consider -infinity

INFORMATION_SCHEMA+system catalog base tables

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

#672. Range upper bound can be NULL

INFORMATION_SCHEMA+system catalog base tables

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

#673. Range upper bound is not restricted

INFORMATION_SCHEMA+system catalog base tables

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

#674. Range upper bound restriction dos not consider infinity

INFORMATION_SCHEMA+system catalog base tables

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

#675. Recursive triggers potentially causing infinite loops

INFORMATION_SCHEMA+system catalog base tables

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

#676. 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)

#677. Redundant CHECK constraints (logical subsumption or equivalence) (empty strings)

INFORMATION_SCHEMA+system catalog base tables

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

#678. Redundant CHECK constraints (logical subsumption or equivalence) (empty strings and strings that consist of whitespace characters)

INFORMATION_SCHEMA+system catalog base tables

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

#679. Redundant CHECK constraints (logical subsumption or equivalence) (empty strings and strings that consist of whitespace characters) (2)

INFORMATION_SCHEMA+system catalog base tables

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

#680. Redundant DO INSTEAD NOTHING rules on naturally non-updatable views

INFORMATION_SCHEMA+system catalog base tables

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