PostgreSQL Query Catalog

Find the use of =NULL and <>NULL in case of table level check constraints, domain level check constraints, WHEN clauses of triggers, WHERE clauses of rules, subqueries of derived tables, and bodies of routines. Write correct code. In order to determine as to whether a value is missing or not one has to use the IS [NOT] NULL predicate. NULL is the marker in SQL that denotes a missing value. Although it is often called "NULL value", one cannot treat it as an ordinary value, i.e., use it in comparisons as a value.

Find the use of regular expressions where logical or is incorrectly specified, i.e., (| or |).

Find trigger functions that are used in case of more than one table. Although it is legal, one must be careful when changing the functions in order to avoid unwanted consequences.

Find trigger functions that contain a conditional (IF or CASE) but do not contain a SELECT statement before these. The latter condition is for the reason that one cannot use a subquery in the WHEN clause. Thus, if one wants to make a query and decide the further action based on the results of the query, then one must do it within the body of the function.

Find trigger names that are used in a database in more than one schema. Different things should have different names. But here different triggers have the same name. Also make sure that this is not a duplication.

Find triggers on base tables that are used to calculate tsvector values that react to a wrong set of events, i.e., react to the DELETE event or do not react to the INSERT and UPDATE events.

Find triggers that get an argument from the CREATE TRIGGER statement.

Find triggers that try prevent updating data in a certain column but prevent also certain legal updates - updates that write to a field a value that was in the field before the update.

Find unique indexes 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 unique index (not associated with a constraint) columns with the names like id, identifikaator, code, kood, number, etc. The names could have underscores as the prefix or suffix. These are too generic names.

Find unlogged tables. These may improve the performance of INSERT operations, but with the price of possibly loosing data - an unlogged table is automatically truncated after a crash or unclean shutdown.

Find unnamed parameters in PL/PGSQL routines that do not declare aliases for parameters and in SQL routines. Avoid unnamed parameters because dependency on position in case of referencing the parameters makes evolving the code more difficult. In case of unnamed parameters - if one changes the order of parameters in the routine signature, then one has to change the body of the routine in order to use correct references. The bigger is the number of parameters in a routine the more the unnamed parameters make it more difficult to understand the routine.

Find updatable views that have WITH LOCAL CHECK OPTION constraint. The predicate of a view is the conjunction of the predicates of its (directly and indirectly) underlying tables (both base tables and derived tables) as well as the predicate of the view itself. In case of using WITH LOCAL CHECK OPTION constraint "New rows are only checked against the conditions defined directly in the view itself. Any conditions defined on underlying base views are not checked (unless they also specify the CHECK OPTION)." (PostgreSQL manual) Thus, use instead WITH CASCADED CHECK option to instruct the system to check new rows against the entire predicate of the view.

Find UPDATE triggers where updated columns are not specified. These triggers could be executed too often because unneeded executions are not prevented.

Find UPDATE triggers where WHEN clause is not specified. These triggers could be executed too often because unneeded executions are not prevented.

Find usage privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.

Find user-defined composite types that are not used in case of any table, column, and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code.

Find user-defined non-trigger routines with no parameters.

Find user-defined non-trigger SQL and PL/pgSQL routines that name starts with "set" (but not with "setting") but do not contain a UPDATE statement.

Find user-defined non-trigger SQL and PL/pgSQL routines where the beginning of the name of the routine indicates a certain action inside the routine (INSERT, UPDATE, or DELETE) but there is no such statement in the routine body.