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.
#521. Minimum tuple length required before trying to move long column values into TOAST tables has been changed
INFORMATION_SCHEMA+system catalog base tablesFind base tables in case of which toast_tuple_target storage parameter value is not the default value (2040). "Changing this value may not be useful for very short or very long rows. Note that the default setting is often close to optimal, and it is possible that setting this parameter could have negative effects in some cases. " Make sure that the parameter has an optimal value.
#522. Missing default values for audit timestamps
INFORMATION_SCHEMA onlyThis query identifies base table columns whose name and data type suggest they store row registration or last modification times, but which lack a default value. In good database design, such audit timestamp columns should typically have a default value (like CURRENT_TIMESTAMP or now()) to ensure the time is recorded automatically.
#523. 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.
#524. 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.
#525. Missing foreign keys on user reference columns
INFORMATION_SCHEMA+system catalog base tablesThis query identifies potential missing foreign key constraints by searching for unconstrained base table columns whose names suggest they record references to a user (e.g., user_id, created_by). It specifically flags columns that are not part of any primary key, unique constraint, or foreign key constraint. To minimize false positives, the query intelligently excludes columns configured with CURRENT_USER or SESSION_USER default values, as these often represent system-level audit logs rather than relational application data. Missing foreign keys on user references can lead to orphaned records and compromise referential integrity.
#526. Missing USAGE privileges on schema
INFORMATION_SCHEMA+system catalog base tablesIf a user has a privilege to use a schema object, then the user must also have the usage privilege on the schema that contains the object.
#527. Mixing different mechanisms to generate surrogate values
INFORMATION_SCHEMA onlyUse the same mechanism of generating surrogate key values throughout the database. The use of SERIAL notation/explicitly creating a sequence generator and declaration of a column as an identity column will cause the creation of an external and internal sequence generator, respectively. Nevertheless, one should try to stick with using one of the mechanisms in order to cause less confusion. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)
#528. Mixing the use of TEXT and VARCHAR type in case of base table columns
INFORMATION_SCHEMA onlyDeclaring a column to have the type TEXT or the type VARCHAR (without the maximum number of characters) has the same end result in terms of what data can be recorded in the column. Nevertheless, one should try to stick with using one of the type names in order to cause less confusion. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)
#529. Modification-time BEFORE UPDATE triggers lacking a WHEN (OLD.* IS DISTINCT FROM NEW.*) condition
INFORMATION_SCHEMA+system catalog base tablesThis query identifies row-level BEFORE UPDATE triggers that update a row's modification timestamp column (e.g., NEW.viimase_muutm_aeg := CURRENT_TIMESTAMP(0);), but lack a WHEN (OLD.* IS DISTINCT FROM NEW.*) condition to verify that the row data actually changed. Without this condition, executing a no-op UPDATE statement (where the new column values are identical to the existing values) still fires the trigger and overwrites the modification timestamp. This corrupts audit accuracy by recording a false modification time and forces PostgreSQL to perform unnecessary physical tuple writes, WAL logging, and index maintenance for a row whose business data did not change.
#530. More than one index on a column
system catalog base tables onlyFind base table columns that belong to more than one index (including automatically created indexes that support constraints).
#531. 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.
#532. 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.
#533. Multiple columns in the same base table that are associated with a sequence generator
INFORMATION_SCHEMA onlyFind base tables where multiple columns are associated with a sequence generator. Do not create unnecessary sequence generators. If one uses in a table a surrogate key, then it is enough to have one column where the values are generated by using a (external or internal) sequence generator.
#534. Multiple constraints with the same name
system catalog base tables onlyThis query identifies table constraints (CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE, and EXCLUDE) that share the exact same name across multiple base or foreign tables. According to the SQL standard, constraint names must be unique within a schema. However, PostgreSQL only enforces constraint name uniqueness at the table level for CHECK and FOREIGN KEY constraints (and at the schema level for index-backed constraints). Allowing multiple tables to use identical constraint names causes several issues:
- Broken INFORMATION_SCHEMA queries: Standard INFORMATION_SCHEMA views (such as check_constraints, referential_constraints, and constraint_column_usage) assume that the pair (constraint_schema, constraint_name) uniquely identifies a constraint. Duplicate names within a schema cause joins across these views to produce Cartesian products and inaccurate metadata results.
- Ambiguous error handling and logging: When a constraint violation occurs, application error handlers, ORMs, and database administrators relying on the constraint name cannot immediately determine which table caused the failure without inspecting additional context.
Constraints that are inherited by or redefined on child tables (INHERITS or declarative partitions) with the same name as on their parent table are excluded, as PostgreSQL table inheritance naturally propagates parent constraint names to child relations.
#535. Multiple deletes from the same table in a routine
INFORMATION_SCHEMA+system catalog base tablesCode should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of deleting different rows of the same table with different DELETE statements one should try to do it with one statement. The query excludes routines where IF statement is used.
#536. Multiple inheritance
system catalog base tables onlyFind instances of multiple inheriance of base tables. Make sure that multiple inheritance is indeed needed.
#537. Multiple RETURNING clauses in a SQL function
INFORMATION_SCHEMA+system catalog base tablesFind SQL functions that have multiple statements with the RETURNING clause.
#538. Multiple selects from the same table in a routine
INFORMATION_SCHEMA+system catalog base tablesCode should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of selecting different fields of the same row with different SELECT INTO statements one should try to do it with one statement.
#539. Multiple simple keys with integer values
INFORMATION_SCHEMA+system catalog base tablesThis query identifies base tables with a potentially redundant key structure, specifically those having more than one PRIMARY KEY or UNIQUE constraint defined on a single integer-type column. This pattern may suggest the presence of multiple surrogate keys for the same entity, which can indicate overcomplicated data model. A single entity should typically have only one system-generated identifier to maintain schema clarity, simplify join logic, and avoid redundancy. The presence of multiple such keys warrants a review to determine if one is superfluous.
#540. Multiple triggers that update tsvector values
system catalog base tables onlyFind base tables that have multiple triggers to update tsvector values.
| # | Name(sorted ascending, activate to sort descending) | Goal(activate to sort ascending) | Type(activate to sort ascending) | Data source(activate to sort ascending) | Last update(activate to sort ascending) | License | Actions |
|---|---|---|---|---|---|---|---|
| 521 | Minimum tuple length required before trying to move long column values into TOAST tables has been changed | Find base tables in case of which toast_tuple_target storage parameter value is not the default value (2040). "Changing this value may not be useful for very short or very long rows. Note that the default setting is often close to optimal, and it is possible that setting this parameter could have negative effects in some cases. " Make sure that the parameter has an optimal value. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 522 | Missing default values for audit timestamps | This query identifies base table columns whose name and data type suggest they store row registration or last modification times, but which lack a default value. In good database design, such audit timestamp columns should typically have a default value (like CURRENT_TIMESTAMP or now()) to ensure the time is recorded automatically. | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 523 | 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 | |
| 524 | 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 | |
| 525 | Missing foreign keys on user reference columns | This query identifies potential missing foreign key constraints by searching for unconstrained base table columns whose names suggest they record references to a user (e.g., user_id, created_by). It specifically flags columns that are not part of any primary key, unique constraint, or foreign key constraint. To minimize false positives, the query intelligently excludes columns configured with CURRENT_USER or SESSION_USER default values, as these often represent system-level audit logs rather than relational application data. Missing foreign keys on user references can lead to orphaned records and compromise referential integrity. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 526 | Missing USAGE privileges on schema | If a user has a privilege to use a schema object, then the user must also have the usage privilege on the schema that contains the object. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 527 | Mixing different mechanisms to generate surrogate values | Use the same mechanism of generating surrogate key values throughout the database. The use of SERIAL notation/explicitly creating a sequence generator and declaration of a column as an identity column will cause the creation of an external and internal sequence generator, respectively. Nevertheless, one should try to stick with using one of the mechanisms in order to cause less confusion. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 528 | Mixing the use of TEXT and VARCHAR type in case of base table columns | Declaring a column to have the type TEXT or the type VARCHAR (without the maximum number of characters) has the same end result in terms of what data can be recorded in the column. Nevertheless, one should try to stick with using one of the type names in order to cause less confusion. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 529 | Modification-time BEFORE UPDATE triggers lacking a WHEN (OLD.* IS DISTINCT FROM NEW.*) condition | This query identifies row-level BEFORE UPDATE triggers that update a row's modification timestamp column (e.g., NEW.viimase_muutm_aeg := CURRENT_TIMESTAMP(0);), but lack a WHEN (OLD.* IS DISTINCT FROM NEW.*) condition to verify that the row data actually changed. Without this condition, executing a no-op UPDATE statement (where the new column values are identical to the existing values) still fires the trigger and overwrites the modification timestamp. This corrupts audit accuracy by recording a false modification time and forces PostgreSQL to perform unnecessary physical tuple writes, WAL logging, and index maintenance for a row whose business data did not change. |
Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 530 | More than one index on a column | Find base table columns that belong to more than one index (including automatically created indexes that support constraints). | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 531 | 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 | |
| 532 | 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 | |
| 533 | Multiple columns in the same base table that are associated with a sequence generator | Find base tables where multiple columns are associated with a sequence generator. Do not create unnecessary sequence generators. If one uses in a table a surrogate key, then it is enough to have one column where the values are generated by using a (external or internal) sequence generator. | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 534 | Multiple constraints with the same name | This query identifies table constraints (CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE, and EXCLUDE) that share the exact same name across multiple base or foreign tables. According to the SQL standard, constraint names must be unique within a schema. However, PostgreSQL only enforces constraint name uniqueness at the table level for CHECK and FOREIGN KEY constraints (and at the schema level for index-backed constraints). Allowing multiple tables to use identical constraint names causes several issues:
Constraints that are inherited by or redefined on child tables (INHERITS or declarative partitions) with the same name as on their parent table are excluded, as PostgreSQL table inheritance naturally propagates parent constraint names to child relations. |
Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 535 | Multiple deletes from the same table in a routine | Code should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of deleting different rows of the same table with different DELETE statements one should try to do it with one statement. The query excludes routines where IF statement is used. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 536 | Multiple inheritance | Find instances of multiple inheriance of base tables. Make sure that multiple inheritance is indeed needed. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 537 | Multiple RETURNING clauses in a SQL function | Find SQL functions that have multiple statements with the RETURNING clause. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 538 | Multiple selects from the same table in a routine | Code should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of selecting different fields of the same row with different SELECT INTO statements one should try to do it with one statement. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 539 | Multiple simple keys with integer values | This query identifies base tables with a potentially redundant key structure, specifically those having more than one PRIMARY KEY or UNIQUE constraint defined on a single integer-type column. This pattern may suggest the presence of multiple surrogate keys for the same entity, which can indicate overcomplicated data model. A single entity should typically have only one system-generated identifier to maintain schema clarity, simplify join logic, and avoid redundancy. The presence of multiple such keys warrants a review to determine if one is superfluous. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 540 | Multiple triggers that update tsvector values | Find base tables that have multiple triggers to update tsvector values. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View |