PostgreSQL Query Catalog

Write correct code "The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null." (PostgreSQL documentation)

Find STATEMENT level triggers that refer to the values of row variables NEW or OLD. NEW and OLD are special variables that can only be used in row-level trigger procedures.

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 the names of database objects where the name of the database object contains a part of the name of the object type. For instance, the query finds base tables, were the name contains fragments _base, base_, _table, or table_.

Find the names of columns where the name of the column contains a part of the name of the data type of the column. For instance, the query finds columns, were the name contains fragments integer_ or _integer.

Find columns of base and foreign tables that based on the column names are used to register durations but the type of the column is time. "It is possible to use a TIME data type if the duration is less than 24 hours, but this is not what the type is intended for, and can be the cause of confusion for the next person who has to maintain your code."

Find columns of base and foreign tables that based on the column names and types are used to register start time and duration rather than start time and end time.

Find columns that probably store content of files in the database.

Find subqueries of derived tables (views, materialized views) with the LIMIT/FETCH clause or with DISTINCT ON construct but without the ORDER BY clause. These constructs require sorting to produce a meaningful result.

Find surrogate keys. Surrogate key is a key that consist of one column, which has an integer type. The key has been declared by using PRIMARY KEY or UNIQUE constraint. The column is associated with a sequence generator (either external or internal, i.e., created by the system automatically because the column has been declared as an identity column). The column does not participate in any foreign key.

Find surrogate key columns that name does not end with "id_" or start with "id_".

Find the names of domain CHECK constraints that have been system-generated. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different.

Find the names of database constraints that have been system-generated. Additional restrictions are that the constraints must involve only one column and are associated directly with a table (not through a domain). Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different.

Find all CHECK constraints (except NOT NULL) that are associated with a base table or a foreign table column and use a regular expression. It is useful to enforce as many constraints at database level as possible. In this way one improves data quality as well as gives extra information to the database users (including the DBMS engines, development environments, and applications).

Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there is no usage of sequence generators, then there is a question as to whether there are no surrogate keys in the database at all (could be possible and OK) or (more probable) developers have forgotten to implement the generation of surrogate keys.

Find CHECK constraints of base table and foreign table columns that are not valid. These constraints have been created so that the existing data has not been checked against the constraint. It could be deliberate in case of legacy systems that have data quality problems. However, ideally all the data in the table conforms to the constraint.

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

Find base table and foreign table constraint names that are used in a database more than once (possibly in different schemas or in case of different types of constraints). 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.

Find table functions that use OFFSET. OFFSET method is a common way for implementing pagination.

Find tables that contain both a state and a status column.