PostgreSQL Query Catalog

Find columns with JSON, JSONB, or XML type. Each columns should have the most appropriate data type.

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.

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.

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.

Find comments that have been registered with a COMMENT statement and that are associated with more than one object. It would probably mean that a comment is incorrect or missing.

Find completely overlapping foreign keys, i.e., the same set of columns of a table is covered by more than one foreign key constraint. These constraints could refer to the same table/key or different tables/keys.

Find completely overlapping key (primary key, unique, and exclude where all operators are =) constraints. This is a form of duplication. It leads to the creation of multiple indexes to the same set of columns.

Find composite foreign keys where the order of columns does not correspond to the order of columns in the referenced candidate key. Find composite foreign keys in case of which the foreign key and candidate key are not the same in terms of data types of the columns. For instance, the query returns information about a foreign key that columns have the types (SMALLINT, INTEGER) that refers to the candidate key that columns have the types (INTEGER, SMALLINT). In SQL keys are ordered sets of columns. Thus, in case of composite foreign key declarations one has to pay attention that the order of columns in the FOREIGN KEY clause matches the order of columns in the REFERENCES clause.

Find composite foreign keys where the order of columns does not correspond to the order of columns in the referenced candidate key. Find composite foreign keys in case of which the foreign key and candidate key consist of columns with the same name but the order of columns in the keys is different. For instance, the query returns information about a foreign key (personal_code, country_code) that refers to the candidate key (country_code, personal_code). In SQL keys are ordered sets of columns. Thus, in case of composite foreign key declarations one has to pay attention that the order of columns in the FOREIGN KEY clause matches the order of columns in the REFERENCES clause.

Find composite foreign keys with a mix of mandatory and optional columns. In case of a composite foreign keys all the columns should either optional or mandatory in order to avoid problems with NULLs.

Find composite indexes that do not support any constraint but are on more than three columns.

Find composite types that are created by a user, i.e., the type is not created automatically by the database management system based on a relation.

Find composite types with the same attributes (regardless of the order of attributes). Make sure that there is no duplication.

Find constraints that are perhaps badly named. Find names of constraints (directly connected to a base table) and non-unique indexes that do not contain the associated column name.

Find constraints that are perhaps badly named. Table names help us to ensure the uniqueness of the names within a schema and make the names more expressive and user-friendly.

Find constraints that involve more than one columns. Check as to whether the names follow a common style or not.

Find database objects that name contains words "and" (English) or "ja" (Estonian).

Find database objects that name contains words "data" or "info". These are noise words because databases are meant for storing and manipulating data/information.

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.