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 user-defined types in the database.
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 11 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	All enumerated types	Find all enumerated types.	General	INFORMATION_SCHEMA+system catalog base tables	2022-10-31 10:19	MIT License	View
2	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
3	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
4	Duplicate enumerated types	Find enumerated types with exactly the same values. There should not be multiple types that have the same values.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
5	Duplicate independent (i.e., not created based on a table) composite types	Find composite types with the same attributes (regardless of the order of attributes). Make sure that there is no duplication.	Problem detection	system catalog base tables only	2021-02-25 17:29	MIT License	View
6	Enumerated types with zero or one value	Fidn enumerated types with zero or one value. Type is a named finite set of values. The empty set is a set. A set with one value is a set. Thus, types with zero or one value are legal. In practical terms each type, usually, should contain at least two values.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
7	Perhaps the type of a base table column should be BOOLEAN (based on enumerated types)	Find base table columns that have an enumerated type that seems to emulate Boolean type.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
8	Too generic names (user-defined types, domains)	Find names of user-defined types and domains that contain too generic (noise) words.	Problem detection	system catalog base tables only	2021-10-31 17:41	MIT License	View
9	Unused composite types (for table columns, typed tables, input and output parameters)	Find user-defined composite types that are not used in case of any table, column, and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code.	Problem detection	system catalog base tables only	2021-02-25 17:30	MIT License	View
10	Unused enumerated types (for base table columns, domains, and parameters)	Find enumerated types that are not used in case of any base table column, domain, and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
11	User-defined composite types	Find composite types that are created by a user, i.e., the type is not created automatically by the database management system based on a relation.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View