PostgreSQL Query Catalog

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 the names of simple primary key columns that name does not follow the pattern _id or _code but it is quite similar.

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 base table columns that do not have uuid type but the name of the column refers to the possibility that the values in the column are uuid's.

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

Make sure that in case of using the type DECIMAL/NUMERIC as the type of a base table column the precision (the permitted number of digits in the number) is not too small. For instance, the biggest value in the type NUMERIC(1,1) is 0.9.

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.

Find base table columns and domains that have a textual type and the default value that represents a truth-value. For instance, the type of a column could be VARCHAR and the column has the default value 'TRUE'.

Specify for each column/domain a right data type that takes into account expected values in the column/domain. Find base table columns and domains that refer to the nextval function by using the default value mechanism but do not have the type INTEGER, SMALLINT, or BIGINT. This check is performed in case of identity columns: ERROR: identity column type must be smallint, integer, or bigint.

Specify for each column/domain a right data type that takes into account expected values in the column/domain. Find base table columns and domains that have a textual type but the default value that represents a number (for instance, '100', '2', or '0.22'). Exclude columns about formats.

Find columns that name points to the possibility that values in this are classifier codes. The column has a numeric type but it is not SMALLINT. Usually each classifier type has so few values that type SMALLINT would be appropriate.

Find base table columns and domains that have a textual type but the default value that represents a temporal value (either a static value or invocation of a function that returns such value). Specify for each column/domain a right data type that takes into account expected values in the column/domain.

Find base table columns that name refers to the possibility that there are registered deadlines but the column does not have a temporal type.

Find base table columns that have CHAR type, where character maximum length is bigger than 1 and the name of the column does not refer to the possibility that the column holds some kind of codes or flags or hash values.

Find columns of base tables where the name of the column has prefix or suffix "id" or has the name "id" but the column does not have an integer type or uuid type. A convention is to use the phrase "id" in the names of surrogate key columns.

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.

This query identifies all base table columns defined with the timestamp without time zone data type. Use of this data type is a potential design flaw as it can lead to ambiguity and bugs when handling data from multiple time zones.

This query identifies all base table columns defined with the range of timestamp without zone data type. Use of this data type is a potential design flaw as it can lead to ambiguity and bugs when handling data from multiple time zones.