PostgreSQL Query Catalog

Find as to whether you sometimes use current_timestamp function and sometimes now() function. These implement the same functionality.

Find as to whether you sometimes use date_part function and sometimes extract function. These implement the same functionality.

Find as to whether you sometimes use regexp_like function and sometimes ~ operator. These implement the same functionality. regexp_like function that was added to PostgreSQL 15 and provides the same functionality as ~ and ~* operators. Try to be consistent.

Find derived tables (views and materialized views) that sort rows based on random values. This can be used to find a random subset of rows. It is a computationally expensive operation.

Find derived tables (views and materialized views) that sort rows based on random values but do not limit the number of rows. This is unnecessary because without sorting the rows are returned in a unspecified order. Sorting based on random values is a computationally expensive operation.

Find routines that contain a statement that sorts rows based on random values. This can be used to find a random subset of rows. It is a computationally expensive operation.

Find routines that contain a statement that sorts rows based on random values but do not limit the number of rows. This is unnecessary because without sorting the rows are returned in a unspecified order. Sorting based on random values is a computationally expensive operation.

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

Write correct code "The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null." (PostgreSQL documentation)

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 database objects that name contains words "data" or "info". These are noise words because databases are meant for storing and manipulating data/information.

Find the names of database objects where the name of the database object contains a part of the name of the object type. For instance, the query finds base tables, were the name contains fragments _base, base_, _table, or table_.

Find the names of columns where the name of the column contains a part of the name of the data type of the column. For instance, the query finds columns, were the name contains fragments integer_ or _integer.

Find columns of base and foreign tables that based on the column names are used to register durations but the type of the column is time. "It is possible to use a TIME data type if the duration is less than 24 hours, but this is not what the type is intended for, and can be the cause of confusion for the next person who has to maintain your code."

Find columns of base and foreign tables that based on the column names and types are used to register start time and duration rather than start time and end time.

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.

This query identifies CHECK constraints that contain redundant logic for handling NULLs, a pattern often arising from a misunderstanding of SQL's three-valued logic (TRUE, FALSE, UNKNOWN). A CHECK constraint's condition only fails on FALSE, implicitly permitting NULLs by evaluating comparisons to UNKNOWN. Consequently, an explicit OR column IS NULL clause is tautological and adds no functional value, as demonstrated by the equivalence of CHECK (price IS NULL OR price > 0) and CHECK (price > 0). Removing this superfluous logic improves constraint clarity and eliminates a marginal but unnecessary computational step.

Find surrogate key columns that name does not end with "id_" or start with "id_".

This query identifies potential classifier (or reference) tables that contain a column populated by a sequence generator. In good database design, tables storing standard reference data should typically use natural keys rather than auto-incrementing surrogate keys.

This query identifies surrogate key columns defined using the legacy, PostgreSQL-specific SERIAL (or BIGSERIAL) pseudo-type. While functional, this notation is not part of the ISO SQL standard. The recommended best practice in modern PostgreSQL versions is to utilize GENERATED AS IDENTITY columns. Identity columns are standard-compliant and offer superior management of underlying sequences and permissions compared to the older SERIAL implementation.