PostgreSQL Query Catalog

Find columns of derived tables (i.e., views and materialized views) where in the creation statement of the table the name of the column has not been specified, i.e., it is generated by the system.

Find columns of derived tables (i.e., views and materialized views) where in the creation statement of the table the name of the column has not been specified, i.e., it is generated by the system.

Use different names to avoid confusion.

Find the columns that name is the same as the name of the type of the column or the domain of the column.

Find columns with BYTEA or OID type. These columns are potentially meant for storing large objects. Each columns should have the most appropriate data type.

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

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

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.

Find columns of base tables and materialized views that have tsvector type.

Find all comments of columns of 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.

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 all the comments that have been added with a COMMENT statement to schemas, sequences, types, domains, and event triggers.

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 foreign keys that consist of more than one column. Make sure that the order of columns in the composite foreign key corresponds to the order of columns in the composite candidate key in the referenced table.

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 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 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 all CHECK constraints that name contains a digit. Make sure that the name and the Boolean expression are consistent.