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	...
81	Column name contains the table name	Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-06 12:38	MIT License	View
82	Column name is the same as the table name	Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Sometimes columns with such names are used as the key columns. Make sure that the naming style is consistent.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-06 12:38	MIT License	View
83	Columns defined in a subtable	Find columns that have been added to a subtable, i.e., these were not defined in its immediate supertable.	General	INFORMATION_SCHEMA+system catalog base tables	2021-01-02 03:22	MIT License	View
84	Columns for registration and update times	Find base table columns that based on the names and data types are meant for registering registration time or update time. Make sure that the columns have the same properties.	General	INFORMATION_SCHEMA only	2023-11-26 16:51	MIT License	View
85	Columns with array or user-defined type	Find columns with an array or a user-defined type. Each columns should have the most appropriate data type.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-19 17:04	MIT License	View
86	Columns with BOOLEAN type that do have a good name	The prefic of the name should be "is_" or "has_" or "can_" (in English) or "on_" (in Estonian). Worse: agreed, kinnitatud. Better: is_agreement, on_kinnitatud.	General	INFORMATION_SCHEMA+system catalog base tables	2024-01-03 09:56	MIT License	View
87	Columns with BYTEA or OID type	Find columns with BYTEA or OID type. These columns are potentially meant for storing large objects. Each columns should have the most appropriate data type.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-19 17:00	MIT License	View
88	Columns with JSON, JSONB, or XML type	Find columns with JSON, JSONB, or XML type. Each columns should have the most appropriate data type.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-19 17:01	MIT License	View
89	Columns with tsvector type	Find columns of base tables and materialized views that have tsvector type.	General	INFORMATION_SCHEMA+system catalog base tables	2023-12-22 12:41	MIT License	View
90	Comments of columns	Find all comments of columns of tables.	General	system catalog base tables only	2023-10-18 14:09	MIT License	View
91	Comments of derived tables	Find comments of derived tables (views and materialized views) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, triggers, rules). Make sure that the comments give relevant, useful, and correct information.	General	system catalog base tables only	2023-01-19 12:14	MIT License	View
92	Comments of non-derived tables	Find comments of non-derived tables (base tables, foreign tables, and partitioned tables) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, constraints, indexes, triggers, rules). Make sure that the comments give relevant, useful, and correct information.	General	system catalog base tables only	2023-01-19 12:30	MIT License	View
93	Comments of routines	Find comments of user-defined routines (functions or procedures) that are registered in the system catalog witht a COMMENT statement. Make sure that the comments give relevant, useful, and correct information.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
94	Comments of schemas, sequences, types, domains, domain constraints, and event triggers	Find all the comments that have been added with a COMMENT statement to schemas, sequences, types, domains, and event triggers.	General	system catalog base tables only	2023-01-19 15:04	MIT License	View
95	Composite foreign keys	Find foreign keys that consist of more than one column. Make sure that the order of columns in the composite foreign key corresponds to the order of columns in the composite candidate key in the referenced table.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
96	Consistency of CHECK constraint name and content	Find all CHECK constraints that name contains a digit. Make sure that the name and the Boolean expression are consistent.	General	INFORMATION_SCHEMA only	2021-12-10 13:21	MIT License	View
97	Consistency of comments of routines	Find user-defined routines that have a comment registered by the COMMENT statement and a comment within the routine body. Make sure that there are no inconsistencies between the comments.	General	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 10:56	MIT License	View
98	Consistency of using generic column names in candidate key columns that are not foreign key columns	Find the names on base table columns that are a part of a candidate key but not a foreign key and contain a generic word (nimi, nimetus, kommentaar, kirjeldus, name, comment, description, etc.). Make sure that naming of these is consistent, i.e., such names always contain the table name or never contain the table name.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 20:53	MIT License	View
99	Consistency of using generic column names in non-candidate key columns that are also not foreign key columns	Find the names on base table columns that are not a part of a candidate key and a foreign key and contain a generic word (nimi, nimetus, kommentaar, kirjeldus, name, comment, description, etc.). Make sure that naming of these is consistent, i.e., such names always contain the table name or never contain the table name.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 20:53	MIT License	View
100	Constraints that are not redefined in a subtable but there is a CHECK constraint that compensates this	Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) but not in its subtable. Exclude constraints where in case of the subtable there is a CHECK that permits only one specific value in the constraint column. The presence of such check would make the design acceptable. Unfortunately, PostgreSQL table inheritance is implemented in a manner that some constraints (CHECK, NOT NULL) are inherited from the supertable but others are not. "All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited." (PostgreSQL documentation)	General	INFORMATION_SCHEMA+system catalog base tables	2020-12-25 16:20	MIT License	View