PostgreSQL Query Catalog

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.

There should not be cycles in hierarchies meaning that the parent must always be specified. In this case a parent must reference to a child or to itself, otherwise it cannot be registered.

Find as to whether foreign key constraints between tables form a cycle. For instance, if table C refers to table B, table B refers to table A, table A refers to table C and all the foreign key columns are mandatory, then one cannot add data to the tables. Is cycle value in the result should not contain value TRUE. One should check manually as to whether the foreign key columns are mandatory or optional.

Find as to whether the database access by users (applications) can take place thanks to PUBLIC privileges, i.e., find as to whether PUBLIC (all current and future users) has CONNECT privilege in the database. PUBLIC gets the privilege by default.

Find non-superusers who have a privilege to use a table or a routine but do not have the privilege to connect to the database.

This query identifies database objects of the same type within the same schema (or container) that have identical names (and parameters in case of routines) when ignoring case. In SQL databases, this confusing scenario typically occurs when mixing quoted (case-sensitive) and unquoted (case-insensitive) identifiers. For example, a single schema could contain both a table named "Client" and another table named client.

Find database object that belong to the database public interface (virtual data layer - consists of routines and derived tables) and that names contain the letters õäöüÕÄÖÜ (Estonian letters with a diacritic). These letters belong to the Estonian alphabet but do not belong to the ASCII character set. Although permitted by the DBMS, such letters might make it more difficult to use the interface by other programs.

Find database object that do not belong to the database public interface (virtual data layer - consists of routines and derived tables) and that names contain the letters õäöüÕÄÖÜ (Estonian letters with a diacritic). These letters belong to the Estonian alphabet but do not belong to the ASCII character set. Although permitted by the DBMS, such letters might make the naming style inconsistent with the naming style of elements that belong to the public interface. If applications access base tables directly, then the letters can cause the same problems as in case of derived tables, i.e., applications may have difficulties with such names.

This query identifies single-column CHECK constraints where the validation logic utilizes operators or functions that are incompatible with the column's native data type. It detects cases where the database must perform implicit casting to evaluate the expression (e.g., performing arithmetic on a TEXT column or string manipulation on a DATE column). Relying on implicit coercion in constraints involves unnecessary computational overhead and frequently indicates a fundamental error in data modeling or constraint formulation.

Find declaratively partitioned tables that have exactly one partition. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10. If there is only one partition, then it raises question as to why the additional complexity associated with partitioning is needed.

Find declaratively partitioned tables that do not have any partitions. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10.

Find all tables where default value is NULL. NULL is the marker that denotes missing value. Implicitly all columns that do not have a default value have the default NULL. There is no need to specify such default value.

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).

Find deferrable foreign key constraint with ON UPDATE RESTRICT or ON DELETE RESTRICT compensating action. Referential actions are carried out before, and are not part of, the checking of a referential constraint. Deferring a referential constraint defers the checking of the

Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys. Find primary key and unique constraints where a proper subset of columns has a check constraint that permits only one value in the column. The candidate key should involve only columns without such constraint.

Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys. Find primary key and unique constraints where a proper subset of columns has an enumeration type that permits only one value in the column. The candidate key should involve only columns without such type.

Find primary key, unique constraints, and exclude constraints wiht only operator = (i.e., sets of columns) that are proper subsets of other primary key, unique, and exclude constraints of the same table. Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys, i.e., the keys should not have redundancy in terms of columns.

Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys. Find primary keys and unique constraints where a proper subset of the key is a column with an associated sequence generator. Most certainly such subset contains unique values. Thus, this subset should be the key, not its superset.

Find pairs of non-partial unique indexes where the columns of a index are a proper subset of the columns of another index. Include indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. Exclude the pairs where both participants have been created to support a constraint. Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define keys based on candidate keys, i.e., the keys should not have redundancy in terms of columns. Defining a unique index essentially means defining a key in the table but it is done at the lower level of abstraction.

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