PostgreSQL Query Catalog

This query assesses the extent of active logic implementation within the database schema. It verifies that there are at least 3 user-defined triggers or rewrite rules present in the database. These objects represent automated tasks that for example enforce complex integrity constraints. Meeting this threshold indicates a baseline competency in utilizing PostgreSQL's event-driven capabilities.

This query assesses the extent of active logic implementation within the database schema. It verifies that there are user-defined triggers or rewrite rules present for at least 3 tasks. These objects represent automated tasks that for example enforce complex integrity constraints. Meeting this threshold indicates a baseline competency in utilizing PostgreSQL's event-driven capabilities.

This query assesses the use of abstraction layers within the database schema. It verifies that there are at least 4 user-defined views present. Views are essential for simplifying complex queries, restricting data access, and presenting specific data perspectives to applications. Meeting this threshold indicates a sufficient implementation of data abstraction and query encapsulation.

You must give rights to use routines to the users/roles that correspond to applications.

You must give privileges to use views to the users/roles that correspond to applications

This query detects internal inconsistency within individual database objects (user-defined routines, views, materialized views). It flags objects that utilize both the standard concatenation operator (||) and variadic concatenation functions (concat() or concat_ws()) within the same definition body. Mixing null-unsafe operators (||) with null-safe functions (concat) in a single routine suggests a lack of coherent logic or an incomplete refactoring effort, potentially leading to confusing behavior regarding NULL handling.

PostgreSQL regular expressions do not have character classes word and letter.

This query identifies SQL statements that attempt to apply explicit row locking (e.g., FOR SHARE, FOR UPDATE) to the result of an aggregate function (e.g., COUNT(*)). This is a semantic error because locking clauses operate on specific physical rows, whereas aggregate functions return a derived scalar value that is decoupled from the underlying row versions. To correctly enforce a lock, the query must select the specific columns (typically the primary key) of the target rows, rather than a computed aggregate.

Find regular expression patterns that use (?i) modifier in any other place than at the beginning of the pattern or (?-i) in any place of the pattern. Such use of the modifiers is not supported by PostgreSQL.

Find user-defined routines that contain an invocation of a system-defined function without providing any argument. The query considers all aggregate functions as well as some popular scalar functions.

Find derived tables that subqueries invoke a PL/pgSQL function. Avoid context switch that is caused by the invocation of PL/pgSQL functions from the subqueries of derived tables.

Use a right predicate in trigger condition in order to ensure that the trigger executes always when it has to but not more often. IS DISTINCT FROM treats NULL as if it was a known value, rather than unknown. It would be relevant if a column that is referenced in the action condition is optional, i.e., permits NULLs.

This query identifies user-defined SQL and PL/pgSQL routines (excluding triggers) that exhibit a semantic inconsistency between their name and return type. It flags non-trigger routines whose names begin with a predicate prefix (e.g., is_, has_, can_, on_) but whose defined return type is not BOOLEAN. Such a mismatch violates the principle of self-documenting code, as the name creates a strong expectation of a TRUE/FALSE return value, which the routine's signature then contradicts.

Avoid using IS NOT DISTINCT FROM because it makes the query planner to avoid using an index.

Avoid using IS NOT DISTINCT FROM because it makes the query planner to avoid using an index.

Find CHECK constraints that refer only to mandatory columns but the Boolean expression has IS NULL condition.

"In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys." (https://www.postgresql.org/docs/current/datatype-json.html)

You should not give wrong information to the database management system.

This query identifies expressions that use both the length() and char_length() functions, which is a strong indicator of a logical flaw. Since these functions are functional synonyms in PostgreSQL (both returning the character count), an expression that compares their results (e.g., length(col) > char_length(col)) may be either tautological or will always evaluate to false. Such code is typically written under the mistaken assumption that length() returns bytes, a behavior seen in other database management systems.

Find non-primary key and non-unique base table foreign key columns that name refers to the possibility that these are used to register references to a state classifier. The column must belong to a foreign key and does not have a default value.