PostgreSQL Design Check Queries

Catalog of PostgreSQL queries for finding information about a PostgreSQL database and its design problems

	Choose collection:
AND	Choose query type:
AND	Choose query reliability:
AND	Choose category:	Queries of this category provide information about possible occurrences of SQL database design antipatterns.
AND	Choose data source:	From where does the query gets its information?
AND	Enter string:
AND	Has fixing queries?

All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
Statistics about the catalog content and project home in GitHub that has additional information.

There are 19 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	Do not always depend on one's parent	Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table.	General	system catalog base tables only	2021-03-12 15:36	MIT License	View
2	Do not always depend on one's parent (INFORMATION_SCHEMA)	Find where a hierarchical structure is implemented in a base table by having a foreign key that refers to a candidate key of the same table. This design is called adjacency list.	General	INFORMATION_SCHEMA only	2021-03-07 10:57	MIT License	View
3	Do not assume you must use files	Find cases where you store images and other media as files outside the database and store in the database only paths to the files.	Problem detection	INFORMATION_SCHEMA only	2021-03-27 16:55	MIT License	View
4	Do not assume you must use files (based on user data)	Find cases where you store images and other media as files outside the database and store in the database only paths to the files.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-03-10 12:58	MIT License	View
5	Do not clone columns	"Split a base table column into multiple columns based on the values in some other column. Each such newly created column has the name, a part of which is a data value from the original tables."(Bill Karwin) Find base tables that have more than one columns with the same type and field size and the difference between the columns are the year or month number at the end of the column name (two or four numbers, preceded by an underscore).	Problem detection	INFORMATION_SCHEMA only	2022-11-28 15:15	MIT License	View
6	Do not clone tables	Find cases where a base table has been split horizontally into multiple smaller base tables based on the distinct values in one of the columns of the original table. Each such newly created table has the name, a part of which is a data value from the original tables. Find base tables that have the same columns (column name, column order, data type) and the difference between the tables are the numbers in the table names (table1, table2, etc.).	Problem detection	INFORMATION_SCHEMA only	2021-03-18 14:43	MIT License	View
7	Do not create multiple columns for the same attribute	Find base tables that implement recording multivalued attribute values with the help of repeating group of columns. Find base tables that have more than one columns with the same type and field size and the difference between the columns are the numbers in the column names (column1, column2, etc.).	Problem detection	INFORMATION_SCHEMA only	2021-03-18 15:57	MIT License	View
8	Do not format comma-separated lists (based on column names)	Find, based on column names, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table or a foreign table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters.	Problem detection	INFORMATION_SCHEMA only	2021-03-10 12:57	MIT License	View
9	Do not format comma-separated lists (based on default values)	Find, based on default values, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table or a foreign table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters.	Problem detection	INFORMATION_SCHEMA only	2023-12-30 10:59	MIT License	View
10	Do not format comma-separated lists (based on user data)	Find, based on the data that users have recoreded in a database, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-03-12 15:02	MIT License	View
11	Do not leave out the referential constraints (based on adjacency list design)	Try to find missing foreign key constraints. Find non-key and non-foreign key columns of base tables that do not have an associated sequence generator, and that name refers to the possibility that the column holds parent identifiers.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-03-18 11:13	MIT License	View
12	Do not leave out the referential constraints (islands)	Try to find missing foreign key constraints. Find base tables that do not participate in any referential constraint (as the referenced table or as the referencing table). These tables are like "islands" in the database schema.	Problem detection	system catalog base tables only	2021-03-10 12:20	MIT License	View
13	Do not leave out the referential constraints (pairs of tables)	Try to find missing foreign key constraints. Find pairs of base table columns that have the similar name, perhaps the same type, and that are not associated through a foreign key relationship.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
14	Do not specify a list of values in a table column definition	Find cases where the list of valid data values in the column is specified in the column definition (in addition to specifying the type of the column) by using, for instance, check constraints or enumerated types. The check constraint is either associated directly with a table or is associated with a domain.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2022-06-09 14:30	MIT License	View
15	Do not use a generic attribute table	Find base tables that implement a highly generic database design (EAV design - Entiry-Attribute-Value design), according to which attribute values are recorded in a generic table that contains attribute-value pairs.	Problem detection	INFORMATION_SCHEMA only	2021-03-07 17:40	MIT License	View
16	Do not use approach that one size fits all (primary key columns)	Find base base tables have the simple primary key that contains the column with the (case insensitive) name id and an integer type. In addition, the primary key values are generated automatically by the system by using a sequence generator.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-03-18 20:58	MIT License	View
17	Do not use approach that one size fits all (unique index columns)	Find base base tables have a simple unique index (not associated with a constraint) that contains the column with the (case insensitive) name id and an integer type. In addition, the key values are generated automatically by the system by using a sequence generator.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-07 15:12	MIT License	View
18	Do not use dual-purpose foreign keys	Find cases where the same column of a base table T is used to record references to multiple base tables. In addition, one has to add additional column to T for holding metadata about the parent table, referenced by the current row.	Problem detection	INFORMATION_SCHEMA only	2021-03-07 10:56	MIT License	View
19	Do not use FLOAT Data Type	Find base table columns that have FLOAT, REAL, or DOUBLE PRECISION type. "The data types real and double precision are inexact, variable-precision numeric types. On all currently supported platforms, these types are implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it." (PostgreSQL documentation) Do not use the approximate numeric types FLOAT, REAL, and DOUBLE PRECISION in order to present fractional numeric data. Due to the use of the IEEE 754 standard the results of calculations with the values, which have one of these types, can be inexact because out of necessity some numbers must be rounded to a value, which is very close. "Comparing two floating-point values for equality might not always work as expected." (PostgreSQL documentation)	Problem detection	INFORMATION_SCHEMA only	2021-03-12 15:41	MIT License	View