PostgreSQL Query Catalog

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 that do not have any column with a timestamp type. In such tables certainly registration time is not recorded. Make sure as to whether recording registration time is necessary.

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 all base tables that have exactly one PRIMARY KEY or UNIQUE constraint. Find and enforce all the keys. Are you sure there are not more keys in the table?

Find base tables that have more than one column with Boolean type.

Find base tables with more than one comment columns

Find base tables with more than one name columns. Perhaps the normalization level of the table is low.

Find base tables that have more than one column with a temporal type (date or timestamp).

Find base tables that have 1000 rows or more.

Find the base tables that belong to the top 5 in terms of the number of rows in the table. There should be test data in the tables.

Find base table and foreign table columns with the Boolean type that has a CHECK constraint that involves only this column. Avoid unnecessary CHECK constraints. The Boolean type contains only two values and there is nothing to check. By creating a check that determines that possible values in the column are TRUE and FALSE, one duplicates the attribute constraint (column has a type). This is a form of duplication.

Find base table columns with the Boolean type that has a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint that involves only this column. Avoid unnecessary constraints. It is quite improbable that there must be such constraints. For instance, a table with PRIMARY KEY () or UNIQUE () constraint can have at most two rows.

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

This query identifies boolean columns in base tables that lack a default value, but whose names suggest they implement a state machine or act as state flags. In good database design, such boolean state indicators should typically have a default value (false or true) to ensure a predictable initial state when new records are created.

This query identifies B-tree indexes where the FILLFACTOR has been explicitly set to 90. Since 90 is the default FILLFACTOR for B-tree indexes in PostgreSQL, this explicit declaration is superfluous. Removing such redundant settings simplifies the schema definition, improves maintainability, and makes intentionally non-default configurations more apparent.

Find base table columns that are covered by a primary key or a unique constraint and that probably have a static default value.