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 data in base tables.
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 13 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	Are the passwords hashed?	Find base table columns that name refers to the possibility that these are used to register passwords. Return a value from each such column. Make sure that the password is not registered as open text.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-10 12:14	MIT License	View
2	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
3	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
4	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
5	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
6	Columns with only one value	Find base table columns that contain only one value. Perhaps it is an unnecessary column. Having only one value is most likely inadequate for testing.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
7	Do not assume you must use files (based on user data)	Find cases where you store images and other media as files outside the database and store in the database only paths to the files.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-03-10 12:58	MIT License	View
8	Do not format comma-separated lists (based on user data)	Find, based on the data that users have recoreded in a database, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-03-12 15:02	MIT License	View
9	Empty columns	Find columns in non-empty tables that do not contain any values. If there are no values in a columns, then it may mean that one hasn't tested constraints that have been declared to the column or implemented by using triggers. It could also mean that such columns are not needed at all.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
10	Empty tables	Find base tables where the number of rows is zero. If there are no rows in a table, then it may mean that one hasn't tested constraints that have been declared to the table or implemented by using triggers. It could also mean that the table is not needed because there is no data that should be registered in the table.	Problem detection	system catalog base tables only	2022-10-21 15:55	MIT License	View
11	Foreign key column has a default value that is not present in the parent table	Find foreign key columns that have a default value that is not present in the parent table. Identify default values that cause violations of the referential constraints.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
12	Number of rows in base tables	Find the number of rows in base tables.	General	system catalog base tables only	2022-10-27 19:44	MIT License	View
13	Password should not be open text	Find base table columns that name refers to the possibility that these are used to register passwords. Find the columns that have a CHECK constraint that seems to determine the minimal or maximal permitted length of the values in the column. Passwords in a database table must be hashed and salted. Checking the strength of the password by using a check constraint is in this case impossible and the check constraints that try to do it should be removed from the database.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View