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	...
261	Cannot register all legal e-mail addresses	Find CHECK constraints on base table or foreign table columns that contain data about e-mail addresses and apply unnecessary restrictions to the these, rejecting potentially some legal addresses. More precisely, find CHECK constraints that prevent registration of e-mail addresses with multiple @ signs.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
262	Cannot register all legal personal names	Find CHECK constraints on base table or foreign table columns that contain data about personal names and apply unnecessary restrictions to the names, rejecting potentially some legal names. Find checks that prohibit a digit or require a letter A-Z.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
263	Cascading update is not needed (based on surrogate keys)	Find foreign key constraints that reference to a candidate key that is a surrogate key, i.e., its values are generated by the system by using sequence generators. Do not use ON UPDATE CASCADE, ON UPDATE SET NULL, and ON UPDATE SET DEFAULT in case of foreign keys that reference to surrogate keys.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-11-28 12:31	MIT License	View
264	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
265	Case sensitive and case insensitive uniqueness of the same simple key	Find sets of columns that have both case sensitive and case insesitive unique constraints enforced based on these columns. In case of textual columns uniqueness could be checked either in case sensitive or case insensitive way. It is a contradiction to check the uniqueneness in both ways in case of the same key.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
266	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
267	Check as to wheteher the names of columns are in the plural or in the singular form (English version) (aggregate view)	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. Show the number of columns that name is in plural or in singular by table type.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2023-03-20 13:19	MIT License	View
268	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	2025-01-15 14:20	MIT License	View
269	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
270	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	2025-01-15 14:19	MIT License	View
271	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
272	Check as to wheteher the names of tables are in the plural or in the singular form (English version) (aggregate view)	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. Show the number of tables that name is in plural or in singular by table type.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2023-01-16 01:09	MIT License	View
273	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	2024-05-31 08:43	MIT License	View
274	CHECK constraint cardinality is zero	Write correct constraints. Usually the constraint expression should refer to at least one column. A domain constraint expression should refer to the stub VALUE. For instance, the constraint CHECK(1=0) that is associated with a table T would prevent adding any rows to T. The value of the Boolean expression of this constraint is always FALSE.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2022-04-22 17:06	MIT License	View
275	CHECK constraints are inconsistent with DEFAULT values	Find table CHECK constraints that involve two columns that have the same default value. However the constraint assumes that the values must be unequal or one value must be bigger than another.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-11-30 09:58	MIT License	View
276	CHECK constraints that perhaps do not consider 'infinity' and '-infinity' special values	Find check constraints of base tables that cover exactly one column where the default value of the column is special value 'infinity' or '-infinity'. Find only such constraints that probably check a range of permitted values but do not consider that one of the values might be 'infinity' or '-infinity'. Such special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-05-08 18:58	MIT License	View
277	CHECK constraints that perhaps incorrectly consider 'infinity' and '-infinity' special values	Such special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION. No value can be bigger than infinity or smaller than -infinity. If the check constraint cheks that a value must be bigger than -infinity or smaller than infinity, then it does not restrict (almost) anything.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2022-12-07 20:13	MIT License	View
278	CHECK constraints with IS NULL	Find CHECK constraints to one column (associated with a base table directly or through domain) that check that the value is missing (IS NULL). Write as simple constraint definitions as possible. By default columns are optional, i.e., they permit NULLs. NULL in a column means that checking of a CHECK constraint on the column results with UNKNOWN. CHECK constraints permit rows in case of which checking results with TRUE or UNKNOWN. In case of a CHECK constraint there is no need to check separately that a value in the column could be missing, i.e., be NULL. Thus, for instance, instead of writing CHECK (price>0 OR price IS NULL) write CHECK (price>0).	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
279	CHECK constraint with pattern matching on non-textual columns	Find base table and foreign table columns that do not have a textual type but have a single-column check constraint that uses pattern matching. The use of a regular expression, a LIKE clause, or a SIMILAR TO clause in order to constrain values in a non-textual column points to the incorrect selection of operator or column data type.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
280	CHECKs are associated with a column instead of the domain of the column	Find simple check constraints (involve one column) that are associated with a base table column instead of the domain of the column. Common checks of data in columns that share the same domain should be described at the level of domain not at the level of columns.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View