PostgreSQL Query Catalog

This query identifies comments on derived tables (views) and routines that are 50 characters or longer and written entirely in uppercase letters. Writing long comments exclusively in capital letters should be avoided, as it significantly reduces readability and is generally perceived as shouting in digital communication.

Find base tables where all he non-primary key columns are optional. Avoid too many optional columns. You have to be extra careful with NULLs in case of formulating search conditions of data manipulation statements.

This query identifies base tables where every unique key (whether enforced by a UNIQUE constraint or a unique index) includes at least one optional (nullable) column. This is a critical design flaw because it allows rows to exist where the identifying values are partially or entirely missing. Furthermore, because standard SQL treats NULL as an unknown state rather than a distinct value, two NULLs are not considered equal. As a result, a standard UNIQUE constraint fails to prevent the insertion of multiple identical rows if they contain NULL values. The query exempts constraints that explicitly use the UNIQUE NULLS NOT DISTINCT clause, which safely treats NULLs as equal.

Find table functions that do not have any parameters. Prefer simpler and more portable solutions.

Find table names that are the same as some routine name. Use different names to avoid confusion.

Find base tables and materialized views that do not have any index.

This query identifies tables with a surrogate primary key (an auto-generated, meaningless single-column identifier) that either lack a natural key entirely or have a flawed natural key constraint. A common design mistake when introducing a surrogate key is forgetting to enforce uniqueness on the actual business identifiers (natural keys). Furthermore, if a UNIQUE constraint includes an optional (nullable) column, it fails to prevent duplicate rows. Because NULL evaluates to an unknown state and is not considered equal to another NULL in standard SQL, multiple rows with identical values and NULLs can be inserted. The query flags these vulnerabilities unless the constraint explicitly uses the UNIQUE NULLS NOT DISTINCT clause, which safely treats NULL values as equal.

This query identifies tables that use a single-column surrogate primary key but lack any other UNIQUE constraints or unique indexes. The absence of additional unique constraints suggests that the natural business key has not been enforced, creating a risk of data duplication that violates business rules. Tables consisting of only a single column are excluded from this check.

This query identifies leaf base tables (tables with no inheriting children) that possess one or more UNIQUE constraints but lack a defined PRIMARY KEY. It is a standard database design best practice to designate one candidate key as the primary key for every table, treating remaining candidate keys as alternate keys. Parent tables are specifically excluded from this check because, in PostgreSQL inheritance, primary keys do not enforce global uniqueness across the entire table hierarchy. Parent tables often serve as abstract templates, meaning primary keys are properly defined only on their respective child tables.

This query identifies leaf base tables (tables with no inheriting children) that completely lack both PRIMARY KEY and UNIQUE constraints. It is a standard database design best practice to define at least one candidate key and designate it as the primary key for every table. Parent tables are specifically excluded from this check because, in PostgreSQL inheritance, primary keys do not enforce global uniqueness across the entire table hierarchy. Instead, parent tables often serve as abstract templates, meaning primary keys are properly defined only on their respective child tables.

Find base tables without any unique constraints and primary key as well as any unique index, whether it is created explicitly by a developer or automatically by the DBMS. The only legitimate reason of such a table is if it is an abstract table that is used to define common columns of subtables.

Do not create unnecessary tables. If a table has cardinality 1 (one column), then most probably the values in this column should not be system generated unique values.

Find base tables where all the columns are optional, i.e., permit NULLs. In such tables can be rows with no identity value and thus indistinguishable from other rows.

Find the base tables where all the unique columns are optional. In such tables there can be rows without values that identify these rows. In this case there can be rows in the table where the values that should identify the row are missing.

Find base tables where uniqueness is achieved by using only unique indexes, i.e., there is at least one unique index but no uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE)

Find base tables where statistics has not been collected at all or it has been lastly collected more than 40 days ago.

Find base table columns that have Boolean type and based on the column name are meant for recording data about gender.

The precision of a timestamp type of a column must be able to accommodate all the fractional seconds of the default value of the column. Find table columns with the type timestamp without time zone(m) or timestamp with time zone(m) that have a default value LOCALTIMESTAMP(n) or CURRENT_TIMESTAMP(n) WHERE n>m.

Find foreign key constraints that reference to a candidate key that is a surrogate key, i.e., its values are generated by the system by using sequence generators. Do not use ON UPDATE CASCADE, ON UPDATE SET NULL, and ON UPDATE SET DEFAULT in case of foreign keys that reference to surrogate keys.

Choose a suitable data type, field size, and default value. If the default value is shorter from the character maximum length, then spaces will be added to the end of the registered value.