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.
#1. All-caps comments on derived tables and routines
INFORMATION_SCHEMA+system catalog base tablesThis query identifies comments on derived tables (views) and routines that are 50 characters or longer and written entirely in uppercase letters. Writing long comments exclusively in capital letters should be avoided, as it significantly reduces readability and is generally perceived as shouting in digital communication.
#2. All event triggers
system catalog base tables onlyFind event triggers, which are not associated to a specific schema object.
#3. A routine is invoked only once
INFORMATION_SCHEMA+system catalog base tablesFind user-defined routines that are invoked by exactly one user-defined routine.
#4. 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.
#5. Columns with exact/floating numeric types have textual default values
INFORMATION_SCHEMA onlyThe default value of a column should belong to the type of the column. The system shouldn't conduct unnecessary type casts.
#6. Columns with only one value
INFORMATION_SCHEMA+system catalog base tablesFind base table columns that contain only one value. Perhaps it is an unnecessary column. Having only one value is most likely inadequate for testing.
#7. Comments of derived tables
system catalog base tables onlyFind comments of derived tables (views and materialized views) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, triggers, rules). Make sure that the comments give relevant, useful, and correct information.
#8. Comments of non-derived tables
system catalog base tables onlyFind comments of non-derived tables (base tables, foreign tables, and partitioned tables) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, constraints, indexes, triggers, rules). Make sure that the comments give relevant, useful, and correct information.
#9. Comments of routines
INFORMATION_SCHEMA+system catalog base tablesFind comments of user-defined routines (functions or procedures) that are registered in the system catalog witht a COMMENT statement. Make sure that the comments give relevant, useful, and correct information.
#10. Comments of schemas, sequences, types, domains, domain constraints, and event triggers
system catalog base tables onlyFind all the comments that have been added with a COMMENT statement to schemas, sequences, types, domains, and event triggers.
#11. Consistency of comments of routines
INFORMATION_SCHEMA+system catalog base tablesFind user-defined routines that have a comment registered by the COMMENT statement and a comment within the routine body. Make sure that there are no inconsistencies between the comments.
#12. Constraint names missing table identifiers or abbreviations
system catalog base tables onlyThis query identifies database constraints that do not follow standard naming conventions, specifically those that fail to incorporate either the parent table's full name or an abbreviation formed from the initial letters of its name components (e.g., an acronym). Embedding the table's identity into the constraint name is crucial: it ensures schema-wide uniqueness and provides clear, immediate context when constraint violations are logged or surfaced to end-users.
#13. Constraints with the same name within the same schema and constraint type
system catalog base tables onlyFind names of foreign key constraints that are used within the same schema more than once. Find names of check constraints that are used within the same schema more than once. Find names of constraint triggers that are used within the same schema more than once. Different things should have different names. But here different constraints have the same name. Also make sure that this is not a sign of duplication.
#14. Coupling of distinct entity lifecycles via shared state classifiers
system catalog base tables onlyThis query identifies a potential domain modeling flaw where multiple distinct entity tables reference a single, shared state classifier table. According to robust design principles, each main entity type should define its own independent state machine and lifecycle. Sharing a classifier creates undesirable coupling; even if the state vocabularies (e.g., 'Active', 'Inactive') appear identical currently, the business logic for distinct entities is likely to diverge over time. Furthermore, reliance on a universal state table often indicates an under-analyzed domain model utilizing overly generic state transitions.
#15. Database object identifiers reaching the maximum byte length
INFORMATION_SCHEMA+system catalog base tablesThis query identifies user-defined database objects whose identifiers are exactly 63 bytes long. By default, PostgreSQL sets the NAMEDATALEN compile-time constant to 64, making 63 bytes the maximum allowed identifier length. When an identifier exceeds this threshold, PostgreSQL automatically truncates it down to 63 bytes. Names hitting this exact boundary are high-risk indicators of silent truncation: the original identifier was likely longer and was cropped by the engine. This can cause subtle name collisions, break application queries that expect the full name, or corrupt domain semantics.
#16. Default should be declared at the level of domain not at the level of base table columns
INFORMATION_SCHEMA+system catalog base tablesFind domains that have been used to define one or more base table non-foreign key columns and all the columns have the same default value that is associated directly with the column not with the domain. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring the default value at the level of the domain and not at the level of base table columns. An exception is when the domain is used to define foreign key columns. In this case, it would be appropriate to define the default value at the column level (because different foreign keys could have different default values).
#17. Delimited identifiers
INFORMATION_SCHEMA+system catalog base tablesDelimited identifiers (quoted identifiers) are case sensitive. Identifiers of database objects should be case insensitive in order to simplify their management.
#18. Derived table on top of another derived table
INFORMATION_SCHEMA+system catalog base tablesDo not build multiple levels of derived tables (views and materialized views) because it will hamper evolvability and understandability of the tables.
#19. Domain based on another domain
INFORMATION_SCHEMA+system catalog base tablesFind domains that have been defined based on another domain. Do not specify domains based on existing domains. This would unnecessarily increase dependencies and complexity.
#20. Domain candidates
INFORMATION_SCHEMA+system catalog base tablesFind column descriptions that are candidates for describing a domain.
| # | 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 |
|---|---|---|---|---|---|---|---|
| 1 | All-caps comments on derived tables and routines | This query identifies comments on derived tables (views) and routines that are 50 characters or longer and written entirely in uppercase letters. Writing long comments exclusively in capital letters should be avoided, as it significantly reduces readability and is generally perceived as shouting in digital communication. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 2 | All event triggers | Find event triggers, which are not associated to a specific schema object. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 3 | 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 | |
| 4 | 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 | |
| 5 | Columns with exact/floating numeric types have textual default values | The default value of a column should belong to the type of the column. The system shouldn't conduct unnecessary type casts. | Problem detection | INFORMATION_SCHEMA only | MIT (opens in new tab) | View | |
| 6 | Columns with only one value | Find base table columns that contain only one value. Perhaps it is an unnecessary column. Having only one value is most likely inadequate for testing. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 7 | Comments of derived tables | Find comments of derived tables (views and materialized views) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, triggers, rules). Make sure that the comments give relevant, useful, and correct information. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 8 | Comments of non-derived tables | Find comments of non-derived tables (base tables, foreign tables, and partitioned tables) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, constraints, indexes, triggers, rules). Make sure that the comments give relevant, useful, and correct information. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 9 | Comments of routines | Find comments of user-defined routines (functions or procedures) that are registered in the system catalog witht a COMMENT statement. Make sure that the comments give relevant, useful, and correct information. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 10 | Comments of schemas, sequences, types, domains, domain constraints, and event triggers | Find all the comments that have been added with a COMMENT statement to schemas, sequences, types, domains, and event triggers. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 11 | Consistency of comments of routines | Find user-defined routines that have a comment registered by the COMMENT statement and a comment within the routine body. Make sure that there are no inconsistencies between the comments. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 12 | Constraint names missing table identifiers or abbreviations | This query identifies database constraints that do not follow standard naming conventions, specifically those that fail to incorporate either the parent table's full name or an abbreviation formed from the initial letters of its name components (e.g., an acronym). Embedding the table's identity into the constraint name is crucial: it ensures schema-wide uniqueness and provides clear, immediate context when constraint violations are logged or surfaced to end-users. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 13 | Constraints with the same name within the same schema and constraint type | Find names of foreign key constraints that are used within the same schema more than once. Find names of check constraints that are used within the same schema more than once. Find names of constraint triggers that are used within the same schema more than once. Different things should have different names. But here different constraints have the same name. Also make sure that this is not a sign of duplication. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 14 | Coupling of distinct entity lifecycles via shared state classifiers | This query identifies a potential domain modeling flaw where multiple distinct entity tables reference a single, shared state classifier table. According to robust design principles, each main entity type should define its own independent state machine and lifecycle. Sharing a classifier creates undesirable coupling; even if the state vocabularies (e.g., 'Active', 'Inactive') appear identical currently, the business logic for distinct entities is likely to diverge over time. Furthermore, reliance on a universal state table often indicates an under-analyzed domain model utilizing overly generic state transitions. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 15 | Database object identifiers reaching the maximum byte length | This query identifies user-defined database objects whose identifiers are exactly 63 bytes long. By default, PostgreSQL sets the NAMEDATALEN compile-time constant to 64, making 63 bytes the maximum allowed identifier length. When an identifier exceeds this threshold, PostgreSQL automatically truncates it down to 63 bytes. Names hitting this exact boundary are high-risk indicators of silent truncation: the original identifier was likely longer and was cropped by the engine. This can cause subtle name collisions, break application queries that expect the full name, or corrupt domain semantics. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 16 | Default should be declared at the level of domain not at the level of base table columns | Find domains that have been used to define one or more base table non-foreign key columns and all the columns have the same default value that is associated directly with the column not with the domain. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring the default value at the level of the domain and not at the level of base table columns. An exception is when the domain is used to define foreign key columns. In this case, it would be appropriate to define the default value at the column level (because different foreign keys could have different default values). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 17 | Delimited identifiers | Delimited identifiers (quoted identifiers) are case sensitive. Identifiers of database objects should be case insensitive in order to simplify their management. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 18 | Derived table on top of another derived table | Do not build multiple levels of derived tables (views and materialized views) because it will hamper evolvability and understandability of the tables. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 19 | Domain based on another domain | Find domains that have been defined based on another domain. Do not specify domains based on existing domains. This would unnecessarily increase dependencies and complexity. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 20 | Domain candidates | Find column descriptions that are candidates for describing a domain. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View |