PostgreSQL Query Catalog

Find routines that use temporal functions CURRENT_TIMESTAMP, LOCALTIMESTAMP, or now() that is inconsistent with the default values of the columns that are used by the routine, e.g., function uses a column with the default value LOCALTIMESTAMP but the routine uses function CURRENT_TIMESTAMP or now().

Find as to whether in the same database more than one of these functions is used at the same time - now(), localtimestamp, current_timestamp.

This query identifies potentially flawed CHECK constraints on columns of type timestamp or a timestamp range (e.g., tstzrange, daterange). It targets range checks where the upper bound of the value or the range is defined using an inclusive operator (<=). This is a common source of bugs, as a condition like column <= '2025-12-31' or UPPER(column) <= '2025-12-31' is interpreted as being up to 00:00:00 on that day, inadvertently excluding the entire last day of the intended period. The more robust pattern is to use an exclusive upper bound, such as column < '2026-01-01'.

Find Boolean expressions, queries, routines, and default values that refer to value 'NULL'. Perhaps NULL was intended instead. 'NULL' is a string (a value) but NULL is a special marker for denoting missing value.

This query identifies database identifiers (types, domains, columns, parameters) that likely violate the snake_case naming convention. It operates on a specific heuristic, flagging any name that begins with a common predicate prefix (is, has, on) but is not immediately followed by an underscore. This pattern is a strong indicator of camelCase (e.g., isActive) or PascalCase (e.g., IsActive) usage, both of which should be refactored to snake_case (e.g., is_active) to maintain a consistent and readable schema.

Find names that perhaps do not use the snake_case naming style because the name starts with the phrase "id", "uuid", "code" , "kood", "key", or "nr" that is not followed by an underscore. Prefer snake_case over PascalCase and camelCase in names.

Find names that perhaps do not use the snake_case naming style because the name ends with the phrase "id", "uuid", "code", "kood", "key", or "nr" that is not preceded by an underscore. Prefer snake_case over PascalCase and camelCase in names.

Find names that perhaps do not use the snake_case naming style because the name contains a long subsection (at least 20 characters) without underscores. Prefer snake_case over PascalCase and camelCase in names.

Find base tables that have a column for last update time but the table does not have associated before update row level trigger for changing the last update time.

This query identifies base tables with a potentially redundant key structure. It specifically targets those having more than one PRIMARY KEY or UNIQUE constraint defined on a single integer-type column whose name matches the surrogate key naming convention (id_* or *_id). This pattern is a strong indicator of multiple surrogate keys for the same entity, which can indicate an overcomplicated data model. A single entity should typically have only one system-generated identifier to maintain schema clarity, simplify join logic, and avoid redundancy. The presence of multiple such keys warrants a review to determine if one is superfluous.

Find derived tables with a search condition that is possible based on a name instead of a code.

Find base table columns that name refers to the possibility that the column is used to record names or textual descriptions but the column seems to have a simple check constraint that restricts spaces in these.

Find database objects that name contains words "and" (English) or "ja" (Estonian).

This query identifies base table columns and domains that utilize CHECK constraints to simulate boolean logic on non-boolean data types. It targets constraints that restrict the domain of permitted values to binary sets, such as {0, 1}, {'Y', 'N'}, {'T', 'F'}, or string literals like 'true'/'false'. While functional, this "pseudo-boolean" pattern is considered suboptimal in PostgreSQL. The native BOOLEAN data type is preferred for its storage efficiency, semantic clarity, and built-in support for logical operators, rendering such manual constraints unnecessary.

This query identifies a semantic mismatch between a column's name and its data type. It flags base table columns that adhere to a predicate-based naming convention (i.e., starting with is_, has_, can_, or on_) but are not defined with the BOOLEAN data type. This is a design flaw as it forces developers to infer and manage truthiness through other types (e.g., INTEGER, CHAR(1)), which undermines schema clarity, requires data type coercion in queries, and can compromise data integrity by permitting non-boolean states.

Find base table columns that have an enumerated type that seems to emulate Boolean type.

Find routine parameters that based on the name seem to hold truth values. Find parameters that name starts with "is_" or "has_" or "can_" or "on_" and that do not have Boolean type.

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

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.