PostgreSQL Query Catalog

One should be consistent in naming, including in the use of suffixes. If you use sufix in the name of a database object, then it should refer to the type of the database object. Do not use different suffixes in the names of database objects that have the same type. Find types of database objects in case of which there are different suffixes in different names.

Too many parameters (in this case four or more) could be a sign of not separating concerns and having a routine that has more than one task.

Character classes are surrounded by two pairs of square brackets.

Find check constraints of base table and foreign table columns that are either associated with more than one column and have at least one AND operation or are associated with exactly one column and have two or more AND operations.

Find PL/pgSQL routines that perhaps unnecessarily contain DECLARE section. More specifically, find routines with the DECLARE section where the only task seems to be raising an exception. The query excludes the cases where the error message is constructed dynamically, i.e., in this case using a variable maybe justifiable.

Find PL/pgSQL routines that perhaps unnecessarily contain DECLARE section. More specifically, find routines with the DECLARE section where the keyword DECLARE is followed by BEGIN, i.e., the DECLARE section is empty.

Find as to whether a database user (except postgres), who is not a superuser, has Create (C) or Temporary (T) privileges to use the database.

This query identifies derived tables (views and materialized views) containing string concatenation logic that introduces potential leading or trailing whitespace. It targets expressions using the concatenation operator (||), concat(), or format() that may inject separators (such as spaces) but lack a surrounding trim() function. This pattern often results in "dangling separators" when one of the concatenated components is null or empty, degrading data quality and presentation.

Find routines with SQL-standard body that seem to update data in a table that has a column for modification time but the routine does not seem to update the modification time while updating the row and the table does not seem to have an UPDATE trigger that changes the modification time.

This query identifies derived tables (views) that utilize the explicit ANSI SQL-92 join syntax with the ON clause. It specifically targets views where join conditions are defined within the FROM clause but explicitly excludes those using the simplified USING syntax. This helps to identify derived tables that subquery could be simplified.

This query identifies logically flawed CHECK constraints on table columns. It specifically searches for constraints that are defined in such a way that they only permit the insertion of an empty string or strings composed entirely of whitespace characters. This behavior is invariably a bug, likely a typo in the constraint's logic (e.g., using ~ instead of !~), which effectively renders the column unusable for storing meaningful data.

Find all primary key and unique constraints of base tables that involve at least one column with personal names. Personal names are not unique and these cannot be (usually) used to guarantee uniqueness. Either one has declared a superkey instead of candidate key (i.e., the key contains a column that is not needed for uniqueness) or the key columns have been found incorrectly.

This query identifies non-foreign key base table columns intended for telephone number storage that lack essential data validation. It targets columns whose names imply phone data (e.g., containing "phone", "tel") but which have no associated simple CHECK constraint validating the presence of numeric digits. Without such a constraint (e.g., a regex check for [0-9]), the column allows for invalid entries such as purely alphabetic strings or email addresses, compromising data integrity. The query assumes that a valid phone number must minimally contain digits.

Find PL/pgSQL functions with consecutive RETURN clauses. Only the first RETURN will be used, others are unnecessary.

The return value of a function cannot be left undefined. If control reaches the end of the top-level block of the function without hitting a RETURN statement, a run-time error will occur. This restriction does not apply to functions with output parameters and functions returning void, however. In those cases a RETURN statement is automatically executed if the top-level block finishes. It also does not apply to trigger functions that only task is to raise an exception.

Find PL/pgSQL that contain a SELECT statement that is not a SELECT … INTO statement. This is not permitted in PL/pgSQL routines.

The value that is returned by a function should depend on something. If you do not have anything meaningful to return (for instance, the function always returns true, false, NULL, or the same number), then write VOID instead of the return type.

This query audits user-defined routine invocations to enforce named parameter notation, specifically targeting calls to routines with multiple input parameters. It identifies invocations using positional notation (e.g., func(a, b)), which couples the calling logic to the specific order of the callee's signature. Crucially, the query excludes invocations of routines that accept a single input parameter. In single-arity contexts, positional notation is structurally unambiguous and widely accepted. The focus is strictly on multi-parameter calls where positional notation increases the risk of argument misalignment during refactoring.

Do not create unnecessary sequence generators.

Find columns of base tables that name refers to the possibility that it contains unique values but the column does not belong to any PRIMARY KEY/UNIQUE constraint. If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint.