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	...
61	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
62	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
63	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
64	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
65	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
66	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
67	Case insensitive search	Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. Find user-defined routines and derived tables (views/materialized views) that have a subquery with case insensitive search (by using the upper or lower function or ILIKE predicate or (?i) modifier of a regular expression pattern).	General	INFORMATION_SCHEMA+system catalog base tables	2021-10-25 16:55	MIT License	View
68	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
69	Check as to wheteher the names of columns are in the plural or in the singular form (English version)	Check as to wheteher the names of table columns are in the plural or in the singular form. Make sure that you are consistent in naming.	General	INFORMATION_SCHEMA+system catalog base tables	2023-03-20 13:18	MIT License	View
70	Check as to wheteher the names of columns are in the plural or in the singular form (Estonian version)	Check as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming.	General	INFORMATION_SCHEMA+system catalog base tables	2023-10-26 17:01	MIT License	View
71	Check as to wheteher the names of parameters are in the plural or in the singular form (English version)	Check as to wheteher the names of routine parameters are in the plural or in the singular form. Make sure that you are consistent in naming.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-16 01:08	MIT License	View
72	Check as to wheteher the names of parameters are in the plural or in the singular form (Estonian version)	Check as to wheteher the names of routine parameters are in the plural or in the singular form. Make sure that you are consistent in naming.	General	INFORMATION_SCHEMA+system catalog base tables	2023-11-15 16:53	MIT License	View
73	Check as to wheteher the names of tables are in the plural or in the singular form (English version)	Check as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-16 01:09	MIT License	View
74	Check as to wheteher the names of tables are in the plural or in the singular form (Estonian version)	Check as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-16 01:09	MIT License	View
75	CHECK constraints on columns with Boolean data	Find check constraints that involve columns with the type Boolean.	General	INFORMATION_SCHEMA only	2020-12-27 15:09	MIT License	View
76	CHECK constraints on columns with personal names	Find CHECK constraints on base table or foreign table columns that contain data about personal names. Make sure that the constraints do not restrict registration of legal names.	General	INFORMATION_SCHEMA only	2023-12-25 12:51	MIT License	View
77	CHECK constraints on columns with temporal data	If your table contains columns with temporal data, then it will be appropriate to restrict the range of possible values in these columns because some of the values that belong to the type might not be appropriate (for instance, imagine a client who was born in 1100-12-03 or a contract that was registered in 3890-12-12- 12:45). If your table contains multiple columns with temporal data that denote events, then the rule about the order of the events must be enforced, if possible.	General	INFORMATION_SCHEMA only	2023-12-25 12:37	MIT License	View
78	CHECK constraints that use non-deterministic functions	Discover incorrect usage of non-deterministic functions in CHECK constraints. Find base table columns and foreign table columns that have a CHECK constraint that refers to a non-deterministic function that returns current date/time/timestamp.	General	INFORMATION_SCHEMA only	2020-11-06 14:51	MIT License	View
79	CHECK constraints with the cardinality bigger than one	Find multicolumn CHECK constraints. Such constraints must be associated directly with a base table, i.e., these cannot be associated with a domain. Enforce as much data integrity as possible at the database level and prefer declarative constraints to a trigger.	General	system catalog base tables only	2023-12-25 12:38	MIT License	View
80	CHECK constraints with the cardinality bigger than one that involve the same set of columns	CHECK constraints with the cardinality bigger than one that involve the same set of columns. Make sure that there is no duplication.	General	system catalog base tables only	2023-12-25 12:39	MIT License	View