Filter Queries

Found 116 queries.

Page 1 of 6
  • 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 tables

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 License: MIT (opens in new tab)

#2. All event triggers

system catalog base tables only

Find event triggers, which are not associated to a specific schema object.

General License: MIT (opens in new tab)

#3. A routine is invoked only once

INFORMATION_SCHEMA+system catalog base tables

Find user-defined routines that are invoked by exactly one user-defined routine.

Problem detection License: MIT (opens in new tab)

#4. CHECKs are associated with a column instead of the domain of the column

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#5. Columns with exact/floating numeric types have textual default values

INFORMATION_SCHEMA only

The default value of a column should belong to the type of the column. The system shouldn't conduct unnecessary type casts.

Problem detection License: MIT (opens in new tab)

#6. Columns with only one value

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#7. Comments of derived tables

system catalog base tables only

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 License: MIT (opens in new tab)

#8. Comments of non-derived tables

system catalog base tables only

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 License: MIT (opens in new tab)

#9. Comments of routines

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#10. Comments of schemas, sequences, types, domains, domain constraints, and event triggers

system catalog base tables only

Find all the comments that have been added with a COMMENT statement to schemas, sequences, types, domains, and event triggers.

General License: MIT (opens in new tab)

#11. Consistency of comments of routines

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#12. Constraint names missing table identifiers or abbreviations

system catalog base tables only

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 License: MIT (opens in new tab)

#13. Constraints with the same name within the same schema and constraint type

system catalog base tables only

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 License: MIT (opens in new tab)

#14. Coupling of distinct entity lifecycles via shared state classifiers

system catalog base tables only

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 License: MIT (opens in new tab)

#15. Database object identifiers reaching the maximum byte length

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#16. Default should be declared at the level of domain not at the level of base table columns

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#17. Delimited identifiers

INFORMATION_SCHEMA+system catalog base tables

Delimited identifiers (quoted identifiers) are case sensitive. Identifiers of database objects should be case insensitive in order to simplify their management.

Problem detection License: MIT (opens in new tab)

#18. Derived table on top of another derived table

INFORMATION_SCHEMA+system catalog base tables

Do not build multiple levels of derived tables (views and materialized views) because it will hamper evolvability and understandability of the tables.

Problem detection License: MIT (opens in new tab)

#19. Domain based on another domain

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#20. Domain candidates

INFORMATION_SCHEMA+system catalog base tables

Find column descriptions that are candidates for describing a domain.

General License: MIT (opens in new tab)
# 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