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	...
521	IS DISTINCT FROM should be used instead of <> in WHEN clauses	Use a right predicate in trigger condition in order to ensure that the trigger executes always when it has to but not more often. IS DISTINCT FROM treats NULL as if it was a known value, rather than unknown. It would be relevant if a column that is referenced in the action condition is optional, i.e., permits NULLs.	Problem detection	INFORMATION_SCHEMA only	2024-12-23 12:29	MIT License	View
522	Is does not return a boolean	Find user-defined SQL and PL/pgSQL non-trigger routines that do not return a truth value (for instance, returns an integer or does not return a value at all) although the name suggest that it should return a truth value (TRUE or FALSE).	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-06 14:25	MIT License	View
523	IS NOT DISTINCT FROM in derived tables	Avoid using IS NOT DISTINCT FROM because it makes the query planner to avoid using an index.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-09-28 13:08	MIT License	View
524	IS NOT DISTINCT FROM in routines	Avoid using IS NOT DISTINCT FROM because it makes the query planner to avoid using an index.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-09-17 13:14	MIT License	View
525	IS NULL check is probably not needed	Find CHECK constraints that refer only to mandatory columns but the Boolean expression has IS NULL condition.	Problem detection	INFORMATION_SCHEMA only	2022-06-09 13:57	MIT License	View
526	JSON type instead of JSONB type	"In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys." (https://www.postgresql.org/docs/current/datatype-json.html)	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
527	LEAKPROOF routines that are perhaps not leakproof	You should not give wrong information to the database management system.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-05 14:12	MIT License	View
528	Length and char_lenfgth functions are used within the same expression	Find expressions that refer to both length and char_length functions. Make sure that the expression is correct.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-09 13:44	MIT License	View
529	Lifecycle not initiated	Find non-primary key and non-unique base table foreign key columns that name refers to the possibility that these are used to register references to a state classifier. The column must belong to a foreign key and does not have a default value.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-26 15:40	MIT License	View
530	LIKE instead of =	Find expressions that use LIKE predicate for precise comparison.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-09 10:43	MIT License	View
531	LIKE with a regular expression pattern	Find expressions that use LIKE (including ILIKE) predicate with a regular expression patterns. In a LIKE pattern one can use only _ and % metasymbols to construct a pattern.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-10-21 10:38	MIT License	View
532	LIKE without a pattern with % or _	Find expressions that use LIKE predicate witout a pattern that contains at least one % or _ sign.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
533	Mandatory columns for holding large textual values (comments, descriptions, etc.)	Find mandatory (NOT NULL) base table columns that name, column type, and field size refers to the possibility that these are used to register large textual values like comments, descriptions, and explanations.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
534	Many-to-many relationship types that do not have additional attributes	Find base tables that implement many-to-many relationship types that do not permit repeating relationships. More specifically find tables that have two or more foreign keys and all the columns of the table are either foreign key columns or a surrogate key column. It could be that during the system analysis a mistake has been made and some attributes of the entity type that represents the relationship type have not been discovered.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2022-01-21 10:48	MIT License	View
535	Many-to-many relationship types that do not permit repeating relationships and do not have additional attributes	Find base tables that implement many-to-many relationship types that do not permit repeating relationships. More specifically find tables that have two or more foreign keys and all the columns of the table belong to a foreign key. In addition, all the table columns must be covered by a primary key or unique constraint. It could be that during the system analysis a mistake has been made and some attributes of the entity type that represents the relationship type have not been discovered.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-03-28 12:04	MIT License	View
536	Meaningless terms in derived tables	Find derived tables that subquery contains terms "foo", "bar", "foobar", or "baz".	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-06 14:08	MIT License	View
537	Meaningless terms in names	Find names of database objects that contain terms "foo", "bar", "foobar", or "baz".	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-12-21 16:36	MIT License	View
538	Meaningless terms in routines	Find routines that subquery contains terms "foo", "bar", "foobar", or "baz".	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-06 14:09	MIT License	View
539	Middle-man	Find a routine that's only task is to invoke another routine. If a routine performs only one action, delegating work to another routine, why does it exist at all?	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-05 12:48	MIT License	View
540	Missing USAGE privileges on schema	If a user has a privilege to use a schema object, then the user must also have the usage privilege on the schema that contains the object.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-21 12:06	MIT License	View