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.
#461. Incorrect field size (based on default values) (2)
INFORMATION_SCHEMA onlyThis query identifies potential schema discrepancies related to column sizing. It flags text columns in base tables where the static default value is unusually short—specifically, less than half the column's maximum allowed capacity.
#462. Incorrect password hash update
INFORMATION_SCHEMA+system catalog base tablesFind row level update triggers that incorrectly implement update of password hash. It should not be that the new password hash is calculated based on the existing hash.
#463. Incorrect prefix of a constraint name or an index name
INFORMATION_SCHEMA+system catalog base tablesIf the name of an object has the prefix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find prefixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use chk_ as the prefix of an index name or pk_ as the prefix of a check constraint name.
#464. Incorrect prevention of the empty string or strings that consist of only spaces in a field
INFORMATION_SCHEMA onlyThis query identifies ineffectual CHECK constraints on base and foreign table columns that incorrectly attempt to prohibit empty or whitespace-only strings using the predicate trim(column_name) IS NOT NULL. Due to PostgreSQL's strict distinction between an empty string ('') and NULL, this check is a tautology; trim('') evaluates to '', and the condition '' IS NOT NULL is always true. The query finds these logically flawed constraints, which fail to provide any data validation and permit the insertion of the exact values they were intended to prevent.
#465. Incorrect reference to a system-defined function in the routine body
INFORMATION_SCHEMA+system catalog base tablesFind user-defined routines that possibly use incorrect name of a system-defined function (currenttimestamp (correct is current_timestamp), currentdate (correct is current_date), currenttime (correct is current_time), local_time (correct is localtime), local_timestamp (correct is localtimestamp),localdate (there is no such function),local_date (there is no such function), sessionuser (correct is session_user), ucase (correct is upper), lcase (correct is lower)). The problem can arise only if the routine uses dynamic SQL. In case of static SQL the DBMS checks the SQL statemen at the creation time and finds out that for instance, SELECT Count(*) AS cnt FROM Emp WHERE hiredate<=currentdate; is incorrect statement because currentdate is not a function name and there is no column currentdate in the table Emp.
#466. Incorrect specification of logical or in regular expressions
INFORMATION_SCHEMA+system catalog base tablesFind the use of regular expressions where logical or is incorrectly specified, i.e., (| or |).
#467. Incorrect suffix of a constraint name or an index name
INFORMATION_SCHEMA+system catalog base tablesIf the name of an object has the suffix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find suffixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use _chk as the suffix of an index name or _pk as the suffix of a check constraint name.
#468. Incorrect use of COUNT(*) with outer joins
INFORMATION_SCHEMA+system catalog base tablesThis query identifies user-defined routines and derived tables (views or materialized views) that use the COUNT(*) aggregate function (without a FILTER clause) alongside an OUTER JOIN and a GROUP BY clause. This combination is a common SQL anti-pattern. When grouping with an outer join, COUNT(*) counts the rows themselves, including the NULL-filled rows generated for non-matching records. Consequently, it inaccurately reports a count of 1 for completely empty groups instead of the correct count of 0. To get accurate results, developers should count a specific non-null column from the joined table.
#469. Incorrect use of non-deterministic functions in CHECK constraints
INFORMATION_SCHEMA onlyThis query identifies CHECK constraints that use non-deterministic time functions (such as now or current_date) in a way that causes initially valid data to become invalid over time. For example, a constraint like localtimestamp(0) > end_date is an anti-pattern because advancing time will eventually violate it. However, safe implementations—such as birth_date < CURRENT_DATE—are permitted, because once the condition is met, it remains true indefinitely as time moves forward.
#470. Indexes using upper() instead of lower()
INFORMATION_SCHEMA+system catalog base tablesThis query identifies expression indexes that apply the upper() function to a column. In PostgreSQL, it is a strong community convention to standardize on the lower() function for case-insensitive indexing and comparisons. Enforcing this convention ensures consistency across the schema and prevents performance issues caused by queries failing to utilize indexes (for example, if a developer queries using lower(col) but the index is built on upper(col)).
#471. Index FILLFACTOR is not default
system catalog base tables onlyThis query generates a list of all indexes with an explicitly configured, non-default FILLFACTOR for the purpose of a performance audit. The query is aware of the different default FILLFACTOR values associated with various index access methods (e.g., 90 for B-tree, 100 for others like GiST/GIN). This allows administrators to quickly identify and review all instances of customized index storage parameters to assess if these non-standard configurations are justified and still effective.
#472. Index names missing table identifiers or abbreviations
system catalog base tables onlyThis query identifies standalone indexes—specifically those not backing a declarative constraint (such as a PRIMARY KEY or UNIQUE constraint)—that fail to follow standard naming conventions. It flags indexes that omit both the parent table's full name and its acronym (an abbreviation formed from the initial letters of its name components). Incorporating the table identifier into the index name is crucial: in PostgreSQL, indexes share the schema-wide namespace with tables, meaning this naming practice prevents collisions and provides immediate clarity when inspecting execution plans, performance statistics, or slow query logs.
#473. INFORMATION_SCHEMA is missing
INFORMATION_SCHEMA+system catalog base tablesMake sure that you do not drop INFORMATION_SCHEMA schema. In this case most of the design checking queries will not work. This schema automatically exists in all databases.
#474. Input parameters that names do not follow the convention to start with _ or p_
INFORMATION_SCHEMA+system catalog base tablesFor the sake of making code better understandable follow naming conventions.
#475. Input parameters with the same name have different types
INFORMATION_SCHEMA+system catalog base tablesThis query analyzes the semantic consistency of routine signatures by identifying named input parameters that share an identical identifier but possess divergent data types across different routines. While this pattern is a prerequisite for valid routine overloading (polymorphism), it can also indicate a lack of standardization in the data dictionary. For example, using the parameter name status to denote an INTEGER in one context and TEXT in another creates ambiguity regarding the parameter's expected domain. The query results should be audited to distinguish intentional overloading from naming inconsistencies.
#476. Installed extensions
system catalog base tables onlyTry to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database.
#477. Insufficient length for international personal codes
INFORMATION_SCHEMA onlyThis query identifies varchar columns in base tables whose names suggest they store national identification numbers (personal codes), specifically in tables that also include a country code column. This combination implies that the table stores personal codes from various countries. To safely accommodate different international formats, the field size of the personal code column should be at least 20 characters. The query flags any such columns that are too short.
#478. Insufficient number of user-defined base tables
INFORMATION_SCHEMA onlyThis query performs a basic structural assessment of the database schema by counting the number of user-defined base tables. It verifies whether the data model meets a minimum complexity threshold, requiring the existence of at least 7 distinct base tables. This metric serves as a proxy for the scope and depth of the implemented domain model.
#479. Insufficient number of user-defined domains
INFORMATION_SCHEMA+system catalog base tablesThis query assesses the utilization of user-defined domains within the database schema. It verifies a specific structural requirement: the database must contain at least one user-defined domain that is referenced by at least two distinct non-foreign key columns in base tables. This metric serves as an indicator of proper domain reuse and data type standardization. The query validates whether the schema design effectively leverages domains to enforce consistent data definitions across multiple attributes.
#480. Insufficient number of user-defined foreign tables
INFORMATION_SCHEMA onlyThis query assesses the utilization of PostgreSQL's Foreign Data Wrapper (FDW) capabilities. It verifies the existence of at least 2 user-defined foreign tables within the database schema. This requirement ensures that the implemented solution demonstrates the ability to integrate and query data from external sources, extending the data model beyond local storage.
| # | 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 |
|---|---|---|---|---|---|---|---|
| 461 | Incorrect field size (based on default values) (2) | This query identifies potential schema discrepancies related to column sizing. It flags text columns in base tables where the static default value is unusually short—specifically, less than half the column's maximum allowed capacity. | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 462 | Incorrect password hash update | Find row level update triggers that incorrectly implement update of password hash. It should not be that the new password hash is calculated based on the existing hash. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 463 | Incorrect prefix of a constraint name or an index name | If the name of an object has the prefix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find prefixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use chk_ as the prefix of an index name or pk_ as the prefix of a check constraint name. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 464 | Incorrect prevention of the empty string or strings that consist of only spaces in a field | This query identifies ineffectual CHECK constraints on base and foreign table columns that incorrectly attempt to prohibit empty or whitespace-only strings using the predicate trim(column_name) IS NOT NULL. Due to PostgreSQL's strict distinction between an empty string ('') and NULL, this check is a tautology; trim('') evaluates to '', and the condition '' IS NOT NULL is always true. The query finds these logically flawed constraints, which fail to provide any data validation and permit the insertion of the exact values they were intended to prevent. | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 465 | Incorrect reference to a system-defined function in the routine body | Find user-defined routines that possibly use incorrect name of a system-defined function (currenttimestamp (correct is current_timestamp), currentdate (correct is current_date), currenttime (correct is current_time), local_time (correct is localtime), local_timestamp (correct is localtimestamp),localdate (there is no such function),local_date (there is no such function), sessionuser (correct is session_user), ucase (correct is upper), lcase (correct is lower)). The problem can arise only if the routine uses dynamic SQL. In case of static SQL the DBMS checks the SQL statemen at the creation time and finds out that for instance, SELECT Count(*) AS cnt FROM Emp WHERE hiredate<=currentdate; is incorrect statement because currentdate is not a function name and there is no column currentdate in the table Emp. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 466 | Incorrect specification of logical or in regular expressions | Find the use of regular expressions where logical or is incorrectly specified, i.e., (| or |). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 467 | Incorrect suffix of a constraint name or an index name | If the name of an object has the suffix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find suffixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use _chk as the suffix of an index name or _pk as the suffix of a check constraint name. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 468 | Incorrect use of COUNT(*) with outer joins | This query identifies user-defined routines and derived tables (views or materialized views) that use the COUNT(*) aggregate function (without a FILTER clause) alongside an OUTER JOIN and a GROUP BY clause. This combination is a common SQL anti-pattern. When grouping with an outer join, COUNT(*) counts the rows themselves, including the NULL-filled rows generated for non-matching records. Consequently, it inaccurately reports a count of 1 for completely empty groups instead of the correct count of 0. To get accurate results, developers should count a specific non-null column from the joined table. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 469 | Incorrect use of non-deterministic functions in CHECK constraints | This query identifies CHECK constraints that use non-deterministic time functions (such as now or current_date) in a way that causes initially valid data to become invalid over time. For example, a constraint like localtimestamp(0) > end_date is an anti-pattern because advancing time will eventually violate it. However, safe implementations—such as birth_date < CURRENT_DATE—are permitted, because once the condition is met, it remains true indefinitely as time moves forward. | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 470 | Indexes using upper() instead of lower() | This query identifies expression indexes that apply the upper() function to a column. In PostgreSQL, it is a strong community convention to standardize on the lower() function for case-insensitive indexing and comparisons. Enforcing this convention ensures consistency across the schema and prevents performance issues caused by queries failing to utilize indexes (for example, if a developer queries using lower(col) but the index is built on upper(col)). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 471 | Index FILLFACTOR is not default | This query generates a list of all indexes with an explicitly configured, non-default FILLFACTOR for the purpose of a performance audit. The query is aware of the different default FILLFACTOR values associated with various index access methods (e.g., 90 for B-tree, 100 for others like GiST/GIN). This allows administrators to quickly identify and review all instances of customized index storage parameters to assess if these non-standard configurations are justified and still effective. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 472 | Index names missing table identifiers or abbreviations | This query identifies standalone indexes—specifically those not backing a declarative constraint (such as a PRIMARY KEY or UNIQUE constraint)—that fail to follow standard naming conventions. It flags indexes that omit both the parent table's full name and its acronym (an abbreviation formed from the initial letters of its name components). Incorporating the table identifier into the index name is crucial: in PostgreSQL, indexes share the schema-wide namespace with tables, meaning this naming practice prevents collisions and provides immediate clarity when inspecting execution plans, performance statistics, or slow query logs. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 473 | INFORMATION_SCHEMA is missing | Make sure that you do not drop INFORMATION_SCHEMA schema. In this case most of the design checking queries will not work. This schema automatically exists in all databases. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 474 | Input parameters that names do not follow the convention to start with _ or p_ | For the sake of making code better understandable follow naming conventions. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 475 | Input parameters with the same name have different types | This query analyzes the semantic consistency of routine signatures by identifying named input parameters that share an identical identifier but possess divergent data types across different routines. While this pattern is a prerequisite for valid routine overloading (polymorphism), it can also indicate a lack of standardization in the data dictionary. For example, using the parameter name status to denote an INTEGER in one context and TEXT in another creates ambiguity regarding the parameter's expected domain. The query results should be audited to distinguish intentional overloading from naming inconsistencies. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 476 | Installed extensions | Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 477 | Insufficient length for international personal codes | This query identifies varchar columns in base tables whose names suggest they store national identification numbers (personal codes), specifically in tables that also include a country code column. This combination implies that the table stores personal codes from various countries. To safely accommodate different international formats, the field size of the personal code column should be at least 20 characters. The query flags any such columns that are too short. | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 478 | Insufficient number of user-defined base tables | This query performs a basic structural assessment of the database schema by counting the number of user-defined base tables. It verifies whether the data model meets a minimum complexity threshold, requiring the existence of at least 7 distinct base tables. This metric serves as a proxy for the scope and depth of the implemented domain model. | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 479 | Insufficient number of user-defined domains | This query assesses the utilization of user-defined domains within the database schema. It verifies a specific structural requirement: the database must contain at least one user-defined domain that is referenced by at least two distinct non-foreign key columns in base tables. This metric serves as an indicator of proper domain reuse and data type standardization. The query validates whether the schema design effectively leverages domains to enforce consistent data definitions across multiple attributes. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 480 | Insufficient number of user-defined foreign tables | This query assesses the utilization of PostgreSQL's Foreign Data Wrapper (FDW) capabilities. It verifies the existence of at least 2 user-defined foreign tables within the database schema. This requirement ensures that the implemented solution demonstrates the ability to integrate and query data from external sources, extending the data model beyond local storage. | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View |