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.
#281. Cascading update is not needed (based on surrogate keys)
INFORMATION_SCHEMA+system catalog base tablesFind foreign key constraints that reference to a candidate key that is a surrogate key, i.e., its values are generated by the system by using sequence generators. Do not use ON UPDATE CASCADE, ON UPDATE SET NULL, and ON UPDATE SET DEFAULT in case of foreign keys that reference to surrogate keys.
#282. Case insensitive search
INFORMATION_SCHEMA+system catalog base tablesRefers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. Find user-defined routines and derived tables (views/materialized views) that have a subquery with case insensitive search (by using the upper or lower function or ILIKE predicate or (?i) modifier of a regular expression pattern).
#283. Case sensitive and case insensitive uniqueness of the same simple key
INFORMATION_SCHEMA+system catalog base tablesFind sets of columns that have both case sensitive and case insesitive unique constraints enforced based on these columns. In case of textual columns uniqueness could be checked either in case sensitive or case insensitive way. It is a contradiction to check the uniqueneness in both ways in case of the same key.
#284. Check as to wheteher the names of columns are in the plural or in the singular form (English version)
INFORMATION_SCHEMA+system catalog base tablesCheck as to wheteher the names of table columns are in the plural or in the singular form. Make sure that you are consistent in naming.
#285. Check as to wheteher the names of columns are in the plural or in the singular form (English version) (aggregate view)
INFORMATION_SCHEMA+system catalog base tablesCheck as to wheteher the names of table columns are in the plural or in the singular form. Make sure that you are consistent in naming. Show the number of columns that name is in plural or in singular by table type.
#286. Check as to wheteher the names of columns are in the plural or in the singular form (Estonian version)
INFORMATION_SCHEMA+system catalog base tablesCheck as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming.
#287. Check as to wheteher the names of parameters are in the plural or in the singular form (English version)
INFORMATION_SCHEMA+system catalog base tablesCheck as to wheteher the names of routine parameters are in the plural or in the singular form. Make sure that you are consistent in naming.
#288. Check as to wheteher the names of parameters are in the plural or in the singular form (Estonian version)
INFORMATION_SCHEMA+system catalog base tablesCheck as to wheteher the names of routine parameters are in the plural or in the singular form. Make sure that you are consistent in naming.
#289. Check as to wheteher the names of tables are in the plural or in the singular form (English version)
INFORMATION_SCHEMA+system catalog base tablesCheck as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming.
#290. Check as to wheteher the names of tables are in the plural or in the singular form (English version) (aggregate view)
INFORMATION_SCHEMA+system catalog base tablesCheck as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming. Show the number of tables that name is in plural or in singular by table type.
#291. Check as to wheteher the names of tables are in the plural or in the singular form (Estonian version)
INFORMATION_SCHEMA+system catalog base tablesCheck as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming.
#292. CHECK constraint cardinality is zero
INFORMATION_SCHEMA+system catalog base tablesWrite correct constraints. Usually the constraint expression should refer to at least one column. A domain constraint expression should refer to the stub VALUE. For instance, the constraint CHECK(1=0) that is associated with a table T would prevent adding any rows to T. The value of the Boolean expression of this constraint is always FALSE.
#293. CHECK constraints are inconsistent with DEFAULT values
INFORMATION_SCHEMA+system catalog base tablesThis query identifies a logical contradiction between data validation rules and default value definitions. It flags CHECK constraints involving two columns where both columns share the same DEFAULT value, yet the constraint enforces a strict inequality (e.g., col1 < col2) or non-equality (col1 <> col2). This configuration is logically flawed because attempting to insert a row using the default values for both columns will result in an immediate constraint violation (as X < X evaluates to false). This renders the default values mutually exclusive and unusable in practice.
#294. CHECK constraints that perhaps do not consider 'infinity' and '-infinity' special values
INFORMATION_SCHEMA+system catalog base tablesFind check constraints of base tables that cover exactly one column where the default value of the column is special value 'infinity' or '-infinity'. Find only such constraints that probably check a range of permitted values but do not consider that one of the values might be 'infinity' or '-infinity'. Such special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION.
#295. CHECK constraints that perhaps incorrectly consider 'infinity' and '-infinity' special values
INFORMATION_SCHEMA+system catalog base tablesSuch special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION. No value can be bigger than infinity or smaller than -infinity. If the check constraint cheks that a value must be bigger than -infinity or smaller than infinity, then it does not restrict (almost) anything.
#296. CHECK constraint with pattern matching on non-textual columns
INFORMATION_SCHEMA+system catalog base tablesThis query identifies a semantic mismatch between data types and constraint logic. It targets base and foreign table columns that are defined with non-textual data types (e.g., INTEGER, DATE, BOOLEAN) but are subject to single-column CHECK constraints utilizing string pattern matching operators (LIKE, SIMILAR TO, or regular expressions). This practice forces implicit casting to text, which is computationally inefficient and indicates a design flaw. It suggests that either the column should utilize a textual data type, or the constraint should be rewritten using operators appropriate for the actual data type (e.g., numeric ranges instead of regex).
#297. CHECKs are associated with a column instead of the domain of the column
INFORMATION_SCHEMA+system catalog base tablesFind simple check constraints (involve one column) that are associated with a base table column instead of the domain of the column. Common checks of data in columns that share the same domain should be described at the level of domain not at the level of columns.
#298. Classifier tables without a name column
INFORMATION_SCHEMA+system catalog base tablesThis query identifies classifier (reference data) tables that do not contain a name column. Since the primary purpose of a reference table is to provide human-readable labels for standardized codes, lacking a name column often indicates an incomplete database design.
#299. Column name contains the table name
INFORMATION_SCHEMA+system catalog base tablesFind columns that have the same name as the table. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent.
#300. Column name is the same as the table name
INFORMATION_SCHEMA+system catalog base tablesFind columns that have the same name as the table. The names may have different uppercase/lowercase characters. Sometimes columns with such names are used as the key columns. Make sure that the naming style is consistent.
| # | 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 |
|---|---|---|---|---|---|---|---|
| 281 | Cascading update is not needed (based on surrogate keys) | Find foreign key constraints that reference to a candidate key that is a surrogate key, i.e., its values are generated by the system by using sequence generators. Do not use ON UPDATE CASCADE, ON UPDATE SET NULL, and ON UPDATE SET DEFAULT in case of foreign keys that reference to surrogate keys. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 282 | Case insensitive search | Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. Find user-defined routines and derived tables (views/materialized views) that have a subquery with case insensitive search (by using the upper or lower function or ILIKE predicate or (?i) modifier of a regular expression pattern). | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 283 | Case sensitive and case insensitive uniqueness of the same simple key | Find sets of columns that have both case sensitive and case insesitive unique constraints enforced based on these columns. In case of textual columns uniqueness could be checked either in case sensitive or case insensitive way. It is a contradiction to check the uniqueneness in both ways in case of the same key. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 284 | Check as to wheteher the names of columns are in the plural or in the singular form (English version) | Check as to wheteher the names of table columns are in the plural or in the singular form. Make sure that you are consistent in naming. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 285 | Check as to wheteher the names of columns are in the plural or in the singular form (English version) (aggregate view) | Check as to wheteher the names of table columns are in the plural or in the singular form. Make sure that you are consistent in naming. Show the number of columns that name is in plural or in singular by table type. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 286 | Check as to wheteher the names of columns are in the plural or in the singular form (Estonian version) | Check as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 287 | Check as to wheteher the names of parameters are in the plural or in the singular form (English version) | Check as to wheteher the names of routine parameters are in the plural or in the singular form. Make sure that you are consistent in naming. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 288 | Check as to wheteher the names of parameters are in the plural or in the singular form (Estonian version) | Check as to wheteher the names of routine parameters are in the plural or in the singular form. Make sure that you are consistent in naming. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 289 | Check as to wheteher the names of tables are in the plural or in the singular form (English version) | Check as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 290 | Check as to wheteher the names of tables are in the plural or in the singular form (English version) (aggregate view) | Check as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming. Show the number of tables that name is in plural or in singular by table type. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 291 | Check as to wheteher the names of tables are in the plural or in the singular form (Estonian version) | Check as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 292 | CHECK constraint cardinality is zero | Write correct constraints. Usually the constraint expression should refer to at least one column. A domain constraint expression should refer to the stub VALUE. For instance, the constraint CHECK(1=0) that is associated with a table T would prevent adding any rows to T. The value of the Boolean expression of this constraint is always FALSE. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 293 | CHECK constraints are inconsistent with DEFAULT values | This query identifies a logical contradiction between data validation rules and default value definitions. It flags CHECK constraints involving two columns where both columns share the same DEFAULT value, yet the constraint enforces a strict inequality (e.g., col1 < col2) or non-equality (col1 <> col2). This configuration is logically flawed because attempting to insert a row using the default values for both columns will result in an immediate constraint violation (as X < X evaluates to false). This renders the default values mutually exclusive and unusable in practice. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 294 | CHECK constraints that perhaps do not consider 'infinity' and '-infinity' special values | Find check constraints of base tables that cover exactly one column where the default value of the column is special value 'infinity' or '-infinity'. Find only such constraints that probably check a range of permitted values but do not consider that one of the values might be 'infinity' or '-infinity'. Such special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 295 | CHECK constraints that perhaps incorrectly consider 'infinity' and '-infinity' special values | Such special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION. No value can be bigger than infinity or smaller than -infinity. If the check constraint cheks that a value must be bigger than -infinity or smaller than infinity, then it does not restrict (almost) anything. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 296 | CHECK constraint with pattern matching on non-textual columns | This query identifies a semantic mismatch between data types and constraint logic. It targets base and foreign table columns that are defined with non-textual data types (e.g., INTEGER, DATE, BOOLEAN) but are subject to single-column CHECK constraints utilizing string pattern matching operators (LIKE, SIMILAR TO, or regular expressions). This practice forces implicit casting to text, which is computationally inefficient and indicates a design flaw. It suggests that either the column should utilize a textual data type, or the constraint should be rewritten using operators appropriate for the actual data type (e.g., numeric ranges instead of regex). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 297 | CHECKs are associated with a column instead of the domain of the column | Find simple check constraints (involve one column) that are associated with a base table column instead of the domain of the column. Common checks of data in columns that share the same domain should be described at the level of domain not at the level of columns. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 298 | Classifier tables without a name column | This query identifies classifier (reference data) tables that do not contain a name column. Since the primary purpose of a reference table is to provide human-readable labels for standardized codes, lacking a name column often indicates an incomplete database design. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 299 | Column name contains the table name | Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 300 | Column name is the same as the table name | Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Sometimes columns with such names are used as the key columns. Make sure that the naming style is consistent. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View |