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.

Find base tables where all unique keys (sets of columns covered by a unique constraint, or a unique index) have at least one optional column. In this case there can be rows in the table where the values that should identify the row are missing. Because NULL is not a value and is not duplicate of another NULL the, follwing is possible: CREATE TABLE Uniq(a INTEGER NOT NULL,
b INTEGER,
CONSTRAINT ak_uniq UNIQUE (a, b));

INSERT INTO Uniq(a, b) VALUES (1, NULL);
INSERT INTO Uniq(a, b) VALUES (1, NULL);

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.

A surrogate key is a primary key that consist of one column. The values of this column do not have any meaning for the user and the system usually generates the values (integers) automatically. In case of defining a surrogate key in a table it is a common mistake to forget declaring existing natural keys in the table. If a key covers an optional column then it does not prevent duplicate rows where some values are missing and other values are equal. Because NULL is not a value and is not duplicate of another NULL the, follwing is possible: CREATE TABLE Uniq(a INTEGER NOT NULL,
b INTEGER,
CONSTRAINT ak_uniq UNIQUE (a, b));

INSERT INTO Uniq(a, b) VALUES (1, NULL);
INSERT INTO Uniq(a, b) VALUES (1, NULL);

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.

A common style is to declare in each base table one of the candidate keys as the primary key. All the other candidate keys would be alternate keys that will be enforce with the help of UNIQUE + NOT NULL constraints.

Find base tables without any unique constraints and the primary key. In such tables there are no restrictions for recording duplicate rows. Each row represents a true proposition about the real world. It does not make the proposition truer if one presents it more than once. Moreover, duplicate rows increase data size. Without keys the DBMS lacks vital information about data in the database that it can internally use to choose better execution plans and in this way improve performance of database operations. The only legitimate reason of such a table is if it is an abstract table that is used to define common columns of subtables.

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.