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 catergory provide information about the database objects that are not used.
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 20 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	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
2	Declaratively partitioned tables without partitions	Find declaratively partitioned tables that do not have any partitions. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
3	Disabled rules	Identify disabled rules. These should be enabled or dropped, otherwise these are dead code.	Problem detection	system catalog base tables only	2022-10-21 11:22	MIT License	View
4	Disabled system triggers (i.e., disabled enforcement of constraints)	These triggers should be enabled because otherwise some important functionality regarding constraints like enforcing referential integrity does not work.	Problem detection	system catalog base tables only	2021-03-12 15:06	MIT License	View
5	Disabled user triggers	Identify disabled triggers. These should be enabled or dropped, otherwise these are dead code.	Problem detection	system catalog base tables only	2021-02-25 17:30	MIT License	View
6	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
7	Empty schemas	Find schemas without schema objects.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2022-11-04 15:30	MIT License	View
8	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
9	Potentially unused sequence generators	Find sequence generators that are not associated with any column through the default value mechanism. Please note, that it is also possible to refer to a sequence generator from a routine or from an application. If these are indeed not used, then these should be dropped, otherwise these are dead code.	Problem detection	INFORMATION_SCHEMA only	2021-03-07 20:56	MIT License	View
10	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
11	Unused domains (for base table columns and parameters)	Find domains that are not used in case of any base 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
12	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
13	Unused foreign data wrappers	Find foreign data wrappers that do not have any associated foreign servers.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
14	Unused foreign servers	Find foreign servers that do not have any associated foreign tables.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
15	Unused named input parameters	Find named input parameters that are not referenced in the routine body. All the parameters that are presented in the routine signature declaration must be used in its body. Otherwise these are dead code elements.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-05 13:03	MIT License	View
16	Unused schemas	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
17	Unused trigger functions	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:29	MIT License	View
18	Very similar (but not equal) routine names	Find pairs of names of different types of routines that are very similar but not equal.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-11-30 12:09	MIT License	View
19	Very similar domain names	Find pairs of names of domains that are very similar or even equal.	Problem detection	INFORMATION_SCHEMA only	2024-11-30 12:07	MIT License	View
20	Very similar table names	Find pairs of names of different types of tables that are very similar or even equal.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-11-30 11:50	MIT License	View