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 CHECK constraints.
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 138 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	All CHECK constraints of domains that are not associated with any table	Find all CHECK constraints (except NOT NULL) of domains that are not associated with any column.	General	INFORMATION_SCHEMA only	2020-11-06 14:51	MIT License	View
2	All table CHECK constraints that cover at leat one column	Find all CHECK constraints (except NOT NULL) that are associated with a base table or a foreign table column. It is useful to enforce as many constraints at database level as possible. In this way one improves data quality as well as gives extra information to the database users (including the DBMS engines, development environments, and applications).	General	INFORMATION_SCHEMA only	2024-01-01 11:27	MIT License	View
3	AND takes precedence over OR	Make sure that Boolean expressions take into account precedence rules of Boolean operators. AND operator has precedence over OR operator.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
4	A predefine character class has been incorrectly specified	Find regular expressions where a predefined character class is incorrectly specified, e.g. [digit] instead of [:digit:].	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-23 12:09	MIT License	View
5	At most one row is permitted in a table (based on check constraints)	Find base tables and foreign tables where based on a check constraint, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a check constraint permits only one possible value in a column, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint.	General	INFORMATION_SCHEMA+system catalog base tables	2022-11-03 15:21	MIT License	View
6	Base table columns permitting e-mail addresses without @ sign	Find non-foreign key base table columns that name refers to the possibility that these are used to register e-mail addresses. Find the columns that do not have any simple CHECK constraint that contains @ sign. A simple check constraint covers a single column. In this case registration of e-mail addresses without @ is most probably not prohibited.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
7	Base table columns permitting empty strings and strings that consist of only whitespace characters	Find non-foreign key columns of base tables that have a textual type and do not have any simple CHECK constraint, i.e., a constraint that involves only one column. Such columns can contain the empty string and strings that consist of only whitespace.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
8	Base table columns permitting empty strings and strings that consist of only whitespace characters (2)	Find non-foreign key columns of base tables that have a textual type and do not have a simple CHECK constraint (i.e., a constraint that involves only one column) that seems to prohibit empty strings and strings that consist of only whitespace as well as a simple CHECK constraint that specifies permitted symbols.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-02 16:52	MIT License	View
9	Base table columns permitting negative prices/quantity	Find non-foreign key base table columns that name refers to the possibility that these are used to register prices/quantities. Find the columns that do not have any simple CHECK constraints, i.e., a constraint that covers only this column. In this case registration of negative price/quantity is most probably not prohibited.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-10-06 14:14	MIT License	View
10	Base table columns permitting telephone numbers without digits	Find non-foreign key base table columns that name refers to the possibility that these are used to register phone numbers. Find the columns that do not have any simple CHECK constraint that references to the character class of digits. A simple check constraint covers a single column. In this case registration of e-mail addresses without digits is most probably not prohibited.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-09 12:52	MIT License	View
11	Base table columns permitting temporal values that may be outside the range of logical values	Find base tables columns with temporal types (date and timestamp) that do not belong to a foreign key and that do not have any associated simple CHECK constraints, i.e., constraint that involves only one column. For instance, in the column registration_time that does not have any associated CHECK constraints could be values '1200-01-01 00:00' or '5900-12-31 00:00'. Rows with these values most probably represent wrong propositions and the system should restrict registration of such data.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-12-17 00:40	MIT License	View
12	Base table columns permitting URLs without a protocol	Find non-foreign key base table columns that name refers to the possibility that these are used to register URLs. Find the columns that do not have any simple CHECK constraint that references to a protocol. A simple check constraint covers a single column. In this case registration of URLs without a protocol is most probably not prohibited.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-01 13:13	MIT License	View
13	Base tables and foreign tables that do not have any CHECK constraints on non-foreign key columns	Identify possibly missing CHECK constraints.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-11-02 10:41	MIT License	View
14	Base tables and foreign tables that have no CHECK constraints	What are the base tables and foreign tables without any associated (directly or through domains) check constraints? A NOT NULL constraint is a kind of CHECK constraint. However, this query does not take into account NOT NULL constraints.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
15	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
16	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
17	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
18	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
19	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
20	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