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.
#41. All user mappings
system catalog base tables onlyFind all user mappings for foreign servers and tables
#42. All user schemas
INFORMATION_SCHEMA onlyFind all the schemas in the database that are not used for the system purposes.
#43. All user triggers that are associated with tables
INFORMATION_SCHEMA+system catalog base tablesFind user-defined triggers that react to data modifications in tables. Triggers should be used only for the tasks that cannot be achieved in a declarative manner, i.e., by declaring a constraint. Triggers of the same table with the same event_manipulation, action_timing, and action_orientation are sorted based on the trigger name. This is the order of execution of triggers.
#44. AND takes precedence over OR
INFORMATION_SCHEMA+system catalog base tablesMake sure that Boolean expressions take into account precedence rules of Boolean operators. AND operator has precedence over OR operator.
#45. A non-parameterized table function instead of a view
INFORMATION_SCHEMA+system catalog base tablesFind table functions that do not have any parameters. Prefer simpler and more portable solutions.
#46. A predefine character class has been incorrectly specified
INFORMATION_SCHEMA+system catalog base tablesFind regular expressions where a predefined character class is incorrectly specified, e.g. [digit] instead of [:digit:].
#47. Are there enough routines that implement database operations?
INFORMATION_SCHEMA+system catalog base tablesFind user-defined routines that implement database operations (comment refers to an operation) but show these only if there are at least eight such routines. Contracts of database operations are specified in the system analysis documentation. The contracts apply the idea of design by contract in the field of databases.
#48. A routine is invoked only once
INFORMATION_SCHEMA+system catalog base tablesFind user-defined routines that are invoked by exactly one user-defined routine.
#49. A setter does not update a table
INFORMATION_SCHEMA+system catalog base tablesFind user-defined non-trigger SQL and PL/pgSQL routines that name starts with "set" (but not with "setting") but do not contain a UPDATE statement.
#50. A state machine is implemented with the help of an enumeration type
INFORMATION_SCHEMA+system catalog base tablesFind implementations of state machines that uses an enumeration type.
#51. A state machine is implemented with the help of a state classifier table
system catalog base tables onlyFind implementations of state machines that use a state classifier table.
#52. A table has the same name as a routine
INFORMATION_SCHEMA+system catalog base tablesFind table names that are the same as some routine name. Use different names to avoid confusion.
#53. At most one row is permitted in a table (based on check constraints)
INFORMATION_SCHEMA+system catalog base tablesFind base tables and foreign tables where based on a check constraint, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a check constraint permits only one possible value in a column, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint.
#54. At most one row is permitted in a table (based on enumeration types)
INFORMATION_SCHEMA+system catalog base tablesFind base tables and foreign tables where based on the type of a column, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a column has an enumeration type with exactly one value, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint.
#55. Authentication routines vulnerable to timing attacks
INFORMATION_SCHEMA+system catalog base tablesThis query identifies SQL and PL/pgSQL authentication routines that are vulnerable to user enumeration via timing attacks. When verifying user credentials, if a routine returns FALSE immediately upon determining that an account does not exist, an attacker can measure execution latency to confirm valid usernames. Because cryptographic password hashing (such as crypt() using bcrypt) is intentionally slow, hashing only for existing users creates an observable timing discrepancy. The query inspects boolean routines handling credentials (accepting 2 or 3 parameters) and flags those that fail to implement a dummy hash fallback—such as pairing a static bcrypt hash with COALESCE—to ensure uniform, constant-time execution.
#56. Avoid using length function
INFORMATION_SCHEMA+system catalog base tablesThis query identifies all expressions that use the non-standard length() function. Although length() is a functional synonym for char_length() in PostgreSQL, its use is discouraged for two primary reasons: char_length() is the SQL-standard function, and length() has different semantics in other database systems (e.g., returning byte length in MySQL). To enhance code portability and prevent semantic ambiguity for developers, this query flags all instances of length() to encourage standardization on the char_length() function.
#57. Base table column name is the same as its domain name
INFORMATION_SCHEMA onlyFind base table columns that have the same name as the domain name or the data type name of the column. The names may have different uppercase/lowercase characters. Columns, domains, and types are different concepts in SQL and perhaps it is better to use different names in case of these.
#58. Base table column of comments/descriptions has an incorrect data type or maximum character length
INFORMATION_SCHEMA onlyFind base table columns that name refers to the possibility that these are used to register comments/descriptions. Find the columns where the data type is not VARCHAR and TEXT or in case of VARCHAR the maximum number of permitted characters is smaller than 1000. In case of determining field sizes choose a size that permits registration of all possible legal values.
#59. Base table column of measurements does not have a correct data type
INFORMATION_SCHEMA onlyThis query identifies a semantic mismatch in data type selection for columns intended to store measurement data. It targets columns whose names imply a quantitative measurement (e.g., "length", "weight", "count", excluding boolean prefixes like "is_") but are not defined with a numeric data type (INTEGER, NUMERIC, etc.). Storing measurements as text (VARCHAR) prevents mathematical operations, aggregation, and proper sorting, and is considered a design flaw.
#60. Base table column of national identification numbers does not have a correct data type
INFORMATION_SCHEMA onlyFind non-textual base table columns that name refers to the possibility that these are used to register national identification numbers (personal codes). The codes can contain additional symbols to numbers.
| # | 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 |
|---|---|---|---|---|---|---|---|
| 41 | All user mappings | Find all user mappings for foreign servers and tables | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 42 | All user schemas | Find all the schemas in the database that are not used for the system purposes. | General | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 43 | All user triggers that are associated with tables | Find user-defined triggers that react to data modifications in tables. Triggers should be used only for the tasks that cannot be achieved in a declarative manner, i.e., by declaring a constraint. Triggers of the same table with the same event_manipulation, action_timing, and action_orientation are sorted based on the trigger name. This is the order of execution of triggers. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 44 | AND takes precedence over OR | Make sure that Boolean expressions take into account precedence rules of Boolean operators. AND operator has precedence over OR operator. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 45 | A non-parameterized table function instead of a view | Find table functions that do not have any parameters. Prefer simpler and more portable solutions. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 46 | A predefine character class has been incorrectly specified | Find regular expressions where a predefined character class is incorrectly specified, e.g. [digit] instead of [:digit:]. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 47 | Are there enough routines that implement database operations? | Find user-defined routines that implement database operations (comment refers to an operation) but show these only if there are at least eight such routines. Contracts of database operations are specified in the system analysis documentation. The contracts apply the idea of design by contract in the field of databases. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 48 | A routine is invoked only once | Find user-defined routines that are invoked by exactly one user-defined routine. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 49 | A setter does not update a table | Find user-defined non-trigger SQL and PL/pgSQL routines that name starts with "set" (but not with "setting") but do not contain a UPDATE statement. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 50 | A state machine is implemented with the help of an enumeration type | Find implementations of state machines that uses an enumeration type. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 51 | A state machine is implemented with the help of a state classifier table | Find implementations of state machines that use a state classifier table. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 52 | A table has the same name as a routine | Find table names that are the same as some routine name. Use different names to avoid confusion. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 53 | At most one row is permitted in a table (based on check constraints) | Find base tables and foreign tables where based on a check constraint, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a check constraint permits only one possible value in a column, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 54 | At most one row is permitted in a table (based on enumeration types) | Find base tables and foreign tables where based on the type of a column, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a column has an enumeration type with exactly one value, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 55 | Authentication routines vulnerable to timing attacks | This query identifies SQL and PL/pgSQL authentication routines that are vulnerable to user enumeration via timing attacks. When verifying user credentials, if a routine returns FALSE immediately upon determining that an account does not exist, an attacker can measure execution latency to confirm valid usernames. Because cryptographic password hashing (such as crypt() using bcrypt) is intentionally slow, hashing only for existing users creates an observable timing discrepancy. The query inspects boolean routines handling credentials (accepting 2 or 3 parameters) and flags those that fail to implement a dummy hash fallback—such as pairing a static bcrypt hash with COALESCE—to ensure uniform, constant-time execution. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 56 | Avoid using length function | This query identifies all expressions that use the non-standard length() function. Although length() is a functional synonym for char_length() in PostgreSQL, its use is discouraged for two primary reasons: char_length() is the SQL-standard function, and length() has different semantics in other database systems (e.g., returning byte length in MySQL). To enhance code portability and prevent semantic ambiguity for developers, this query flags all instances of length() to encourage standardization on the char_length() function. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 57 | Base table column name is the same as its domain name | Find base table columns that have the same name as the domain name or the data type name of the column. The names may have different uppercase/lowercase characters. Columns, domains, and types are different concepts in SQL and perhaps it is better to use different names in case of these. | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 58 | Base table column of comments/descriptions has an incorrect data type or maximum character length | Find base table columns that name refers to the possibility that these are used to register comments/descriptions. Find the columns where the data type is not VARCHAR and TEXT or in case of VARCHAR the maximum number of permitted characters is smaller than 1000. In case of determining field sizes choose a size that permits registration of all possible legal values. | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 59 | Base table column of measurements does not have a correct data type | This query identifies a semantic mismatch in data type selection for columns intended to store measurement data. It targets columns whose names imply a quantitative measurement (e.g., "length", "weight", "count", excluding boolean prefixes like "is_") but are not defined with a numeric data type (INTEGER, NUMERIC, etc.). Storing measurements as text (VARCHAR) prevents mathematical operations, aggregation, and proper sorting, and is considered a design flaw. | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 60 | Base table column of national identification numbers does not have a correct data type | Find non-textual base table columns that name refers to the possibility that these are used to register national identification numbers (personal codes). The codes can contain additional symbols to numbers. | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View |