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	...
801	Views with unnecessary security invoker	Find views with security invoker option that do not have any underlying base table with a security policy. Security invoker option of views is possible starting from PostgreSQL 15. "The main use case (and the one that inspired the feature) is to be able to use views and still check row-level security policies on the underlying tables as the invoker."	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-17 18:39	MIT License	View
802	Views with WHERE but without security barrier	Find views that do not have the security barrier option but restrict rows in some way.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-12-13 19:29	MIT License	View
803	Wrong comment - trigger function does not implement a database operation	Trigger functions should not contain references to database operations. Perhaps the trigger implements ensuring some invariant of the operation but it does not implement the operation itself.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
804	YELLING IN COMMENTS!	Find comments of derived tables and routines that consist of only uppercase letters. Do not use only uppercase (capital) letters in order to write comments. It means yelling and also makes text less readable.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-04-25 15:13	MIT License	View
805	All clustered indexes	Find all tables that rows have been physically sorted based on an index.	General	system catalog base tables only	2022-10-31 10:17	MIT License	View
806	All database privileges	Find the privileges to use the database. c - CONNECT; C - CREATE; T - TEMPORARY.	General	system catalog base tables only	2021-11-23 20:28	MIT License	View
807	All event triggers	Find event triggers, which are not associated to a specific schema object.	General	system catalog base tables only	2020-12-24 14:54	MIT License	View
808	All exclude constraints	Find all exclude constraints.	General	system catalog base tables only	2021-12-16 11:21	MIT License	View
809	All foreign key constraints	Enforce referential integrity in database. Find all referential integrity (foreign key) constraints.	General	system catalog base tables only	2023-12-07 12:48	MIT License	View
810	All identifying relationships	Find all non-identifying relationships where the foreign key (a set of columns) is a subset of a uniqueness constraint (primary key, unique, or exclude constraint).	General	system catalog base tables only	2023-11-01 11:32	MIT License	View
811	All key constraints	Find all the primary key and unique constraints of base tables.	General	system catalog base tables only	2021-10-16 10:37	MIT License	View
812	All non-identifying relationships	Find all non-identifying relationships where the foreign key (a set of columns) is not a subset of any uniqueness constraint (primary key, unique, or exclude constraint).	General	system catalog base tables only	2023-11-01 11:31	MIT License	View
813	All rules	Find user-defined rules for rewriting data manipulation language statements. Rules should be used only for the tasks that cannot be achieved in a declarative manner, i.e., for example, by declaring a constraint.	General	system catalog base tables only	2023-12-22 12:30	MIT License	View
814	All security policies	Find all security policies.	General	system catalog base tables only	2023-11-17 18:41	MIT License	View
815	All supertables	Find all the base tables that serve as supertables in the inheritance hierarchies	Sofware measure	system catalog base tables only	2021-03-14 17:07	MIT License	View
816	All system-defined TOAST-able types	Find system-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table).	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
817	All user-defined TOAST-able types	Find user-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table).	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
818	All user mappings	Find all user mappings for foreign servers and tables	General	system catalog base tables only	2020-11-11 17:00	MIT License	View
819	A state machine is implemented with the help of a state classifier table	Find implementations of state machines that use a state classifier table.	General	system catalog base tables only	2023-11-26 15:39	MIT License	View
820	Base table columns where TOASTing is possible	Find all base table columns in case of which the system can use TOAST technique.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View