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 996 queries.

Seq nr	Name	Goal	Type▲	Data source	Last update	License	...
861	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
862	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
863	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
864	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
865	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
866	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
867	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-08-07 16:23	MIT License	View
868	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
869	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
870	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
871	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
872	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
873	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
874	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
875	UPDATE triggers where updated columns have not been specified (the trigger could executed too often)	Find UPDATE triggers where updated columns are not specified. These triggers could be executed too often because unneeded executions are not prevented.	Problem detection	INFORMATION_SCHEMA only	2024-12-23 09:53	MIT License	View
876	UPDATE triggers where WHEN clause has not been specified (the trigger could executed too often)	Find UPDATE triggers where WHEN clause is not specified. These triggers could be executed too often because unneeded executions are not prevented.	Problem detection	INFORMATION_SCHEMA only	2024-12-23 09:54	MIT License	View
877	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
878	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
879	Useless type indication	Find columns and parameters where the type of the identifier is perhaps explicitly indicated in the name.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-10 14:47	MIT License	View
880	Useless type indication (2)	Find columns and parameters where the type of the identifier is perhaps explicitly indicated in the name.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-10 14:55	MIT License	View