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	...
381	Check as to wheteher the names of parameters are in the plural or in the singular form (English version)	Check as to wheteher the names of routine parameters are in the plural or in the singular form. Make sure that you are consistent in naming.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-16 01:08	MIT License	View
382	The generic names (columns) (aggregate view)	Find the too generic column names and the number of their occurrences.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-15 10:34	MIT License	View
383	Too generic names (columns)	Find the names of columns that are too generic. In SQL databases data/information is represented as values in columns. It is not a good style to use generic words like data, information, column, etc. In the names of columns. Moreover, avoid too generic column names like: id, tyyp, kood, aeg, kp,type, code, time, date, fk, pk.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-15 10:33	MIT License	View
384	Base table columns with the same name have different types	Find base table columns that have the same name but different type. In general, base tables columns that have the same name should have the same type as well.	Problem detection	INFORMATION_SCHEMA only	2023-01-14 20:54	MIT License	View
385	Consistency of using generic column names in candidate key columns that are not foreign key columns	Find the names on base table columns that are a part of a candidate key but not a foreign key and contain a generic word (nimi, nimetus, kommentaar, kirjeldus, name, comment, description, etc.). Make sure that naming of these is consistent, i.e., such names always contain the table name or never contain the table name.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 20:53	MIT License	View
386	Consistency of using generic column names in non-candidate key columns that are also not foreign key columns	Find the names on base table columns that are not a part of a candidate key and a foreign key and contain a generic word (nimi, nimetus, kommentaar, kirjeldus, name, comment, description, etc.). Make sure that naming of these is consistent, i.e., such names always contain the table name or never contain the table name.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 20:53	MIT License	View
387	Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with exactly one column	Find patterns of the names of constraints and indexes. Make sure that the naming is consistent.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 20:52	MIT License	View
388	Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with two or three columns	Find patterns of the names of constraints and indexes. Make sure that the naming is consistent.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 20:52	MIT License	View
389	Patterns of the names of triggers and rules	Find the patterns of the names of trigger and rule names. Make sure that the naming is consistent.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 20:51	MIT License	View
390	The number of derived table columns with an array, a user-defined, XML, JSON, or JSONB type	Find the number of derived table columns with json, xml, array, or user-defined types. Such columns may contain data that has been aggregated/composed based on values in base tables.	Sofware measure	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 20:22	MIT License	View
391	Paramtetes with an array type, XML, JSON, or JSONB type	Find parameters of user-defined routines that type is an array type, xml, json, or jsonb type. Make sure that the parameter name matches the type (perhaps should be in plural).	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 20:21	MIT License	View
392	Transform method does not return	Find user-defined SQL and PL/pgSQL routines that do not return a value although the name suggest that it should return a value (contains "_to_").	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 19:37	MIT License	View
393	Table has multiple columns for free-form descriptions	Find tables that contain multiple columns for free-form textual descriptions. Make sure that the names of columns are understandable and sufficiently different. Make sure that there are no duplicate columns.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 15:36	MIT License	View
394	Table has both state and status columns	Find tables that contain both a state and a status column.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-14 15:26	MIT License	View
395	Names of triggers and rules that do contain the table name	The names should contain table name in order to make the names better understandable.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-13 19:23	MIT License	View
396	Names of triggers and rules that do not contain the table name	The names should contain table name in order to make the names better understandable.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-13 19:18	MIT License	View
397	Inconsistent use of plural and singular in column names in the context of a relationship (English version)	Find foreign key constraints that cover one column in case of which the name of refererenced/referencing column is in plural and the name of referencing/refererenced column is in singular.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-13 14:14	MIT License	View
398	Inconsistent use of plural and singular in table names in the context of a relationship (English version)	Find foreign key constraints in case of which the name of one of the tables is in plural and the name of another table is in singular.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-13 13:41	MIT License	View
399	Names of indexes that do not contain the table name	Find indexes that do not support a declarative constraint and that are perhaps badly named. Table names make the names more expressive and user-friendly.	Problem detection	system catalog base tables only	2023-01-10 18:13	MIT License	View
400	Names of constraints (directly connected to a base table) that do not contain the table name	Find constraints that are perhaps badly named. Table names help us to ensure the uniqueness of the names within a schema and make the names more expressive and user-friendly.	Problem detection	system catalog base tables only	2023-01-10 18:03	MIT License	View