PostgreSQL Query Catalog

Find sequence generators that are not owned by a table column, i.e., if one drops the table or the column, then the sequence generator stays in place.

Find foreign keys with SET DEFAULT compensatory action where the foreign key column does not have a default value. Compensatory actions cannot make changes that violate integrity constraints in a database. SET DEFAULT means that there shoud be a default value at the foreign key column.

Find foreign key constraints that use a SET NULL compensating action but a foreign key column is mandatory, i.e., does not permit NULLs. Compensatory actions cannot make changes that violate integrity constraints in a database. SET NULL cannot put NULL to a mandatory column (delete a foreign key value).

Find derived tables (views and materialized views) that use a set theoretic operation (union, except or intersect) in a manner that does not remove duplicate rows and thus can produce a multiset not a set. Make sure that it is what is needed.

Find cases where two candidate keys of the same table that are also foreign keys reference to each other.

Find pairs of tables that have both a mandatory (NOT NULL) and not defrerrable foreign key that references to the other table. Such cycles can involve more than two tables but the query detects only cycles with two tables.

Find all the base table columns that have the type time without time zone or time with time zone. Return the data only if there is at least one column with the type time without time zone and one column with the type time with time zone.

Find all the base table columns that have the type timestamp without time zone or timestamp with time zone. Return the data only if there is at least one column with the type timestamp without time zone and one column with the type timestamp with time zone.

Find simple check constraints, i.e., check constraints that cover one column that seem to have multiple tasks. The corresponding code smell in case of cleaning code is "G30: Functions Should Do One Thing". (Robert C. Martin, Clean Code)

Find simple primary keys that column name does not contain the table name. The naming should be clear and consistent.

Find tables that have one column or zero columns.

Find foreign key constraints in case of which some candidate key values cannot be used as foreign key values. Primary key/unique columns and foreign key columns should have the same data type and field size. If, for instance, the primary key column has type INTEGER and foreign key column has type SMALLINT, then one cannot use all the primary key values as foreign key values.

Find cases where some CHECKS are associated with a domain and some with the base table columns that have the domain. Avoid duplication of code. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring CHECKS at the level of the domain and not at the level of base table columns.

Find as to whether there are data modification routines that return a value as well as data modification routines that do not return a value.

Find routines where comments contain TODO phrase.

Find as to whether you sometimes use current_timestamp function and sometimes now() function. These implement the same functionality.

Find as to whether you sometimes use date_part function and sometimes extract function. These implement the same functionality.

Find as to whether you sometimes use regexp_like function and sometimes ~ operator. These implement the same functionality. regexp_like function that was added to PostgreSQL 15 and provides the same functionality as ~ and ~* operators. Try to be consistent.

Find derived tables (views and materialized views) that sort rows based on random values. This can be used to find a random subset of rows. It is a computationally expensive operation.

Find derived tables (views and materialized views) that sort rows based on random values but do not limit the number of rows. This is unnecessary because without sorting the rows are returned in a unspecified order. Sorting based on random values is a computationally expensive operation.