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	...
661	Simple check constraints with multiple tasks	Find simple check constraints, i.e., check constraints that cover one column that seem to have multiple tasks. The corresponding code smell in case of cleaning code is "G30: Functions Should Do One Thing". (Robert C. Martin, Clean Code)	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-01 11:42	MIT License	View
662	Simple natural primary keys	Find primary keys that consist of one column and that values are not generated by the system.	General	INFORMATION_SCHEMA+system catalog base tables	2021-03-08 00:47	MIT License	View
663	Small tables	Find tables that have one column or zero columns.	General	INFORMATION_SCHEMA+system catalog base tables	2021-03-07 10:52	MIT License	View
664	Some candidate key values cannot be used as foreign key values	Find foreign key constraints in case of which some candidate key values cannot be used as foreign key values. Primary key/unique columns and foreign key columns should have the same data type and field size. If, for instance, the primary key column has type INTEGER and foreign key column has type SMALLINT, then one cannot use all the primary key values as foreign key values.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-03-10 12:13	MIT License	View
665	Some CHECKS are associated with a domain and some with the base table columns that have the domain	Find cases where some CHECKS are associated with a domain and some with the base table columns that have the domain. Avoid duplication of code. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring CHECKS at the level of the domain and not at the level of base table columns.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
666	Some data modification functions return a value and some not	Find as to whether there are data modification routines that return a value as well as data modification routines that do not return a value.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-12-16 00:02	MIT License	View
667	Something is still to do in routines	Find routines where comments contain TODO phrase.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-12-11 12:44	MIT License	View
668	Sometimes current_timestamp, sometimes now()	Find as to whether you sometimes use current_timestamp function and sometimes now() function. These implement the same functionality.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-11-21 13:20	MIT License	View
669	Sometimes extract, sometimes date_part	Find as to whether you sometimes use date_part function and sometimes extract function. These implement the same functionality.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-11-21 13:18	MIT License	View
670	Sometimes regexp_like, sometimes ~	Find as to whether you sometimes use regexp_like function and sometimes ~ operator. These implement the same functionality. regexp_like function that was added to PostgreSQL 15 and provides the same functionality as ~ and ~* operators. Try to be consistent.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-11-21 12:36	MIT License	View
671	Sorting rows based on random values in derived tables	Find derived tables (views and materialized views) that sort rows based on random values. This can be used to find a random subset of rows. It is a computationally expensive operation.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
672	Sorting rows based on random values in derived tables without limiting rows	Find derived tables (views and materialized views) that sort rows based on random values but do not limit the number of rows. This is unnecessary because without sorting the rows are returned in a unspecified order. Sorting based on random values is a computationally expensive operation.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
673	Sorting rows based on random values in routines	Find routines that contain a statement that sorts rows based on random values. This can be used to find a random subset of rows. It is a computationally expensive operation.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-10-25 16:56	MIT License	View
674	Sorting rows based on random values in routines without limiting rows	Find routines that contain a statement that sorts rows based on random values but do not limit the number of rows. This is unnecessary because without sorting the rows are returned in a unspecified order. Sorting based on random values is a computationally expensive operation.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-10-25 16:57	MIT License	View
675	SQL function does not return a value	Find SQL functions that do not return a value (return VOID) but the SQL statement in the function has RETURNING clause.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-02-27 17:28	MIT License	View
676	SQL functions that use optimistic approach for locking but do not return a value	Find SQL functions that use a hidden column of PostgreSQL tables - xmin - to implement optimistic locking but do not return any information to the invoker of the functions, i.e., whether the update/delete operation succeeded or not. The functions should let their invokers know as to whether the function succeeded in updating or deleting a row.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-11-30 10:02	MIT License	View
677	SQL routines that return the value of an input parameter	Find SQL routines that return the value of an input parameter.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2025-01-20 14:23	MIT License	View
678	STATEMENT level triggers and ROW level AFTER triggers without RETURN NULL	Write correct code "The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null." (PostgreSQL documentation)	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
679	STATEMENT level triggers that refer to the values of row variables NEW or OLD	Find STATEMENT level triggers that refer to the values of row variables NEW or OLD. NEW and OLD are special variables that can only be used in row-level trigger procedures.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-12-20 14:23	MIT License	View
680	Stating the obvious	Find database objects that name contains words "data" or "info". These are noise words because databases are meant for storing and manipulating data/information.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 17:14	MIT License	View