PostgreSQL Query Catalog

Find SQL functions that do not return a value (return VOID) but the SQL statement in the function has RETURNING clause.

Find SQL functions that have multiple statements with the RETURNING clause.

Find SQL-language routines that have the body that is string literal, i.e., the body is not SQL-standard function body. Routines with a SQL-standard body are permitted starting from PostgreSQL 14.

Find stable routines that contain INSERT, UPDATE, DELETE, SELECT … FOR UPDATE, or LOCK statement. The DBMS should get a correct background information about the behaviour of a routine. Stable routines cannot modify data in a database, lock tables, or its specific rows.

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 statistics about how many base tables have how many derived tables that use these tables.

Find statistics about how many derived tables have how many different underlying tables.

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 key columns that do not have an associated sequence generator. Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there are no 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 surrogate key columns that name does not end with "id_" or start with "id_".

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 keys where the generator can output the same value more than once. Key values must be unique, i.e., at some point the generator will prevent adding new rows to the table.

Find system-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table).

Find table and domain CHECK constraints that compare the result of a Boolean expression with a Boolean value. If you can choose between two logically equivalent Boolean expressions choose the more simple expression.

Find table columns with CHAR or VARCHAR type that have a default value that length is longer from the character maximum length of the column. Choose a suitable data type, field size, and default value. If the value is longer, then it is impossible to register it in a field, i.e., it makes registration of data impossible (except if the excessive characters are spaces).

Find table columns with timestamp/date types that data type and dynamically found default value have a different type.

Find table columns with time types, which data type and dynamically found default value have a different type.

Find table constraints (constraints that are associated directly with the table) that have too generic names like "key" or the name contain too generic words like "data" (all constraints restrict data in the table), or the name is an abbreviation of a constraint type name.

Find table functions that do not have any parameters. Prefer simpler and more portable solutions.

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