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	...
41	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
42	At most one row is permitted in a table (based on enumeration types)	Find base tables and foreign tables where based on the type of a column, 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 column has an enumeration type with exactly one value, 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:18	MIT License	View
43	Base table columns for recording geographic coordinates (based on column names)	Find base table columns that are according to the name meant for recording coordinates.	General	INFORMATION_SCHEMA only	2021-10-08 11:59	MIT License	View
44	Base table columns for storing username, password, and salt	Find base table columns that name refers to the possibility that these are used to register usernames, passwords, and salt. Password should not be open text. It should be a hash value that has been hashed by using salt.	General	INFORMATION_SCHEMA only	2020-11-06 14:51	MIT License	View
45	Base table columns where TOASTing is possible	Find all base table columns in case of which the system can use TOAST technique.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
46	Base table columns where TOAST-ing strategy has been changed	Find base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed so that it is different than the default strategy determined by the type. Make sure that the new strategy is optimal.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
47	Base table columns with a composite type	Find base table columns with a composite type. Think through as to whether a column with a composite type could be replaced with a separate table.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
48	Base table columns with an array type	Find base table columns with an array type. Think through as to whether a column with an array type could be replaced with a separate table.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
49	Base table columns with CHAR(n) or VARCHAR(n) type	Find base table columns with CHAR(n) or VARCHAR(n) type and make sure that n is not too big or too small. Also make sure that you do not use CHAR(n) in case of columns that have to keep variable length strings. "Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way" (https://www.postgresql.org/docs/current/datatype-character.html)	General	INFORMATION_SCHEMA only	2020-11-06 14:51	MIT License	View
50	Base table columns with CITEXT type	Find base table columns with CITEXT type and make sure that case insensitivity is really needed in case of this column.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
51	Base table columns with DECIMAL (p, s) or NUMERIC (p, s) type	Find base table columns with DECIMAL (p, s) or NUMERIC (p, s) type and make sure that precision p and scale s are not too big or too small.	General	INFORMATION_SCHEMA only	2020-11-06 14:51	MIT License	View
52	Base table columns with one of the following types: BIGINT, INTEGER, TEXT, or VARCHAR without max character length	Each column should have the most appropriate data type. Developers sometimes misuse BIGINT, INTEGER, TEXT or VARCHAR type in places where a type that permits smaller values would be more appropriate and semantically descriptive.	General	INFORMATION_SCHEMA only	2020-11-06 14:51	MIT License	View
53	Base table columns with SMALLINT or BOOLEAN type	Each column should have the most appropriate data type. Developers sometimes forget to use SMALLINT type even if it is logically the best choice. Developers also sometimes forget to use BOOLEAN type and instead invent something.	General	INFORMATION_SCHEMA only	2020-11-06 14:51	MIT License	View
54	Base table columns with UUID type	Each column should have the most appropriate data type. If one does not want to have in a table the surrogate key that values are generated by using a sequence generator, then one may use instead a column with Universally Unique Identifiers as the key column.	General	INFORMATION_SCHEMA only	2021-03-07 20:57	MIT License	View
55	Base table FILLFACTOR is not 100	Find all base tables where FILLFACTOR is not 100, i.e., the default value.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
56	Base table has a national identification number as a key	Find base table columns that name refers to the possibility that these are used to register national identification numbers (personal codes). Find the columns that constitute a key of a table. This in turn enforces a (restrictive) business rule that all persons of interest come from one country. Make sure that the enforced constraint is valid, i.e., there is indeed such rule in the domain.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
57	Base tables created based on a type	Find base tables that have been created based on a composite type and thnk through as to whether it was really needed.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
58	Base tables that do not have a TOAST table	Find base tables that (due to the types of their columns) do not have an associated TOAST table for storing out-of-line data.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
59	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
60	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