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:
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 961 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
101	Base tables that have only the surrogate key and do not have any other column	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.	Problem detection	INFORMATION_SCHEMA only	2021-03-08 00:41	MIT License	View
102	Base tables where all the columns are optional	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.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
103	Base tables where all the unique columns are optional	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.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2022-10-21 01:47	MIT License	View
104	Base tables where certainly registration time is not recorded	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.	General	INFORMATION_SCHEMA only	2021-02-26 00:41	MIT License	View
105	Base tables where uniqueness is achieved by using only unique indexes	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)	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2022-10-21 01:54	MIT License	View
106	Base tables, which statistics is probably not up to date	Find base tables where statistics has not been collected at all or it has been lastly collected more than 40 days ago.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
107	Base tables with exactly one key	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?	General	system catalog base tables only	2021-10-16 10:39	MIT License	View
108	Base tables with multiple Boolean columns	Find base tables that have more than one column with Boolean type.	General	INFORMATION_SCHEMA only	2021-02-19 17:41	MIT License	View
109	Base tables with multiple comment columns	Find base tables with more than one comment columns	General	INFORMATION_SCHEMA only	2021-04-02 12:21	MIT License	View
110	Base tables with multiple name columns	Find base tables with more than one name columns. Perhaps the normalization level of the table is low.	General	INFORMATION_SCHEMA only	2021-04-02 12:21	MIT License	View
111	Base tables with multiple temporal columns	Find base tables that have more than one column with a temporal type (date or timestamp).	General	INFORMATION_SCHEMA only	2021-02-19 17:37	MIT License	View
112	Base tables with plenty of data	Find base tables that have 1000 rows or more.	General	system catalog base tables only	2022-10-21 11:25	MIT License	View
113	Base tables with the biggest number of rows	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.	General	system catalog base tables only	2022-10-21 01:48	MIT License	View
114	BOOLEAN base table and foreign table columns with a CHECK constraint that involves olnly this column	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.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
115	BOOLEAN base table and foreign table columns with a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint that involves olnly this column	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.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2022-11-03 10:46	MIT License	View
116	Boolean column for gender	Find base table columns that have Boolean type and based on the column name are meant for recording data about gender.	Problem detection	INFORMATION_SCHEMA only	2022-04-18 00:57	MIT License	View
117	Candidate key columns that have a static default value	Find base table columns that are covered by a primary key or a unique constraint and that probably have a static default value.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-12 11:32	MIT License	View
118	Candidate keys and foreign keys of tables that participate in an inheritance hierarchies	Find primary key, unique, foreign key, and exclude constraints that have been defined in tables that participate in an inheritance hierarchy. Do not forget to redefine the constraints that are defined on supertables also on their subtables.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
119	Candidate keys where all columns have a static default value	Find base table primary key and unique constraints where all columns probably have a static default value. Candidate key columns (columns that belong to the primary key or to an unique constraints) shouldn't have static default values unless you are enforcing a rule that a table can have at most one row. The point of default values is that system assigns them automatically. There cannot be multiple rows with the same key value.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-12 11:33	MIT License	View
120	Cannot accommodate all the fractional seconds in case of table columns	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.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View