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	...
901	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
902	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
903	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
904	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
905	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
906	Unused indexes	Find indexes that are not used by the DBMS. Remember that indexes are not a "free lunch" and they slow down the processes of updating data.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-19 00:23	MIT License	View
907	Unused indexes (2)	Find indexes that are not used by the DBMS. Remember that indexes are not a "free lunch" and they slow down the processes of updating data.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-19 00:28	MIT License	View
908	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
909	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
910	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
911	Updatable views that do not have WITH CHECK OPTION constraint	Find updatable views that do not have WITH CHECK OPTION constraint. WITH CHECK OPTION constraint prevents updates through the view that violate the predicate of the view. Such updates must be prevented.	Problem detection	INFORMATION_SCHEMA only	2023-10-29 10:33	MIT License	View
912	Updatable views that have not been turned to read only	Find views that are theoretically updatable but do not have INSTEAD OF trigger or DO INSTEAD NOTHING rule to prevent data modifications through the view.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-10-29 10:20	MIT License	View
913	Updatable views with WHERE clause that do not have WITH CHECK OPTION constraint	Find updatable views that restrict rows, i.e., have WHERE clause, but do not have WITH CHECK OPTION constraint. WITH CHECK OPTION constraint prevents updates through the view that violate the predicate of the view. Such updates must be prevented.	Problem detection	INFORMATION_SCHEMA only	2024-01-14 17:11	MIT License	View
914	Updataple foreign tables that refer to another PostgreSQL table	Find foreign tables that have been defined based on postgresql_fwd foreign data wrapper and that are updatable. Make sure that updatability is in this case needed.	General	INFORMATION_SCHEMA only	2020-11-06 17:15	MIT License	View
915	Update prevention may prevent legal updates	Find triggers that try prevent updating data in a certain column but prevent also certain legal updates - updates that write to a field a value that was in the field before the update.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2022-07-07 13:12	MIT License	View
916	UPDATE triggers	Find all UPDATE triggers. Make sure that they specify a correct set of columns in which data modification will fire the trigger.	General	INFORMATION_SCHEMA only	2023-12-22 09:47	MIT License	View
917	UPDATE triggers that maybe execute too often	Do not let the system to do extra work. Ensure that trigger procedures are executed only if there is a real need of that. Find UPDATE triggers that could be executed too often because unneeded executions are not prevented.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
918	Updating or deleting data in a routine without restricting rows	Find user-defined routines that contain UPDATE or DELETE statement but do not contain any WHERE clause, meaning that at least one UPDATE or DELETE operation influences all the rows of a table.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-29 13:21	MIT License	View
919	Usage of base tables	Find for each derived table the list of base tables that are used by the derived table.	General	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 00:50	MIT License	View
920	Use invocation of a precise function instead of casting in a default value expression	Be precise and write as little code as possible. Prefer expressions with simple invocations of functions like localtimestamp, current_timestamp, and current_date over expressions like (now())::date. Find table columns that have a default value that casts the type of the returned value of a non-deterministic function (now, localtimestamp, current_timestamp, and current_date).	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View