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 996 queries.

Seq nr	Name	Goal▲	Type	Data source	Last update	License	...
81	Deferrable constraints	Find all deferrable constraints.	General	system catalog base tables only	2021-10-08 11:25	MIT License	View
82	All derived tables that use joining tables	Find all derived tables that join two or more tables.	General	INFORMATION_SCHEMA+system catalog base tables	2024-10-05 18:34	MIT License	View
83	All domains	Find all domains.	General	INFORMATION_SCHEMA only	2020-11-10 11:49	MIT License	View
84	All enumerated types	Find all enumerated types.	General	INFORMATION_SCHEMA+system catalog base tables	2022-10-31 10:19	MIT License	View
85	All exclude constraints	Find all exclude constraints.	General	system catalog base tables only	2021-12-16 11:21	MIT License	View
86	All foreign tables	Find all foreign tables.	General	INFORMATION_SCHEMA only	2020-11-06 15:20	MIT License	View
87	All table functions	Find all functions that return a set of rows.	General	INFORMATION_SCHEMA+system catalog base tables	2023-10-29 11:39	MIT License	View
88	Index FILLFACTOR is not default	Find all indexes where FILLFACTOR is not default, i.e., it has been changed. The default is different in case of different index types is different. In case of B-tree indexes the default is 90.	General	system catalog base tables only	2024-11-21 09:28	MIT License	View
89	Find all non-foreign key columns of base tables	Find all non-foreign key columns of base tables. Make sure that no foreign key constraint is missing.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-21 02:59	MIT License	View
90	All identifying relationships	Find all non-identifying relationships where the foreign key (a set of columns) is a subset of a uniqueness constraint (primary key, unique, or exclude constraint).	General	system catalog base tables only	2023-11-01 11:32	MIT License	View
91	All non-identifying relationships	Find all non-identifying relationships where the foreign key (a set of columns) is not a subset of any uniqueness constraint (primary key, unique, or exclude constraint).	General	system catalog base tables only	2023-11-01 11:31	MIT License	View
92	Personal names are unique	Find all primary key and unique constraints of base tables that involve at least one column with personal names. Personal names are not unique and these cannot be (usually) used to guarantee uniqueness. Either one has declared a superkey instead of candidate key (i.e., the key contains a column that is not needed for uniqueness) or the key columns have been found incorrectly.	Problem detection	system catalog base tables only	2021-02-25 17:29	MIT License	View
93	Chains of ON DELETE CASCADE	Find all referential paths (chains of of parent-child tables that are associated through foreign key constraints) where all foreign key constraints have ON DELETE CASCADE compensating actions. Be careful with too long chains.	General	system catalog base tables only	2022-10-29 20:04	MIT License	View
94	Extension routines	Find all routines that belong to an extension.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
95	All security policies	Find all security policies.	General	system catalog base tables only	2023-11-17 18:41	MIT License	View
96	All sequence generators	Find all sequence generators.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
97	All clustered indexes	Find all tables that rows have been physically sorted based on an index.	General	system catalog base tables only	2022-10-31 10:17	MIT License	View
98	Default is NULL	Find all tables where default value is NULL. NULL is the marker that denotes missing value. Implicitly all columns that do not have a default value have the default NULL. There is no need to specify such default value.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
99	Should the time zone be recorded in case of time or not?	Find all the base table columns that have the type time without time zone or time with time zone. Return the data only if there is at least one column with the type time without time zone and one column with the type time with time zone.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
100	Should the time zone be recorded in case of timestamp or not?	Find all the base table columns that have the type timestamp without time zone or timestamp with time zone. Return the data only if there is at least one column with the type timestamp without time zone and one column with the type timestamp with time zone.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View