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

Seq nr	Name	Goal	Type	Data source	Last update▼	License	...
321	The same CHECK has a different name in different places	Find the names of table CHECK constraints that have the same Boolean expression but a different naming style in different places (tables). The naming of constraints should be consistent. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)	Problem detection	system catalog base tables only	2023-01-14 20:47	MIT License	View
322	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
323	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
324	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
325	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
326	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
327	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
328	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
329	Inconsistent use of plural and singular in table names in the context of a relationship (Estonian 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 14:21	MIT License	View
330	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
331	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
332	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
333	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
334	Number of system-generated and user-defined constraint names by constraint type (constraints that involve more than one column)	Find the number of system-generated constraint names by constraint type. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different.	Sofware measure	system catalog base tables only	2023-01-10 14:53	MIT License	View
335	Using system-defined names of constraints (constraints that involve more than one column)	Find the constraint types in case of which there exists system-defined names.	Problem detection	system catalog base tables only	2023-01-10 14:53	MIT License	View
336	Using system-defined names of constraints (constraints that involve one column)	Find the constraint types in case of which there exists system-defined names.	Problem detection	system catalog base tables only	2023-01-10 14:53	MIT License	View
337	Number of system-generated and user-defined constraint names by constraint type (constraints that involve one column)	Find the number of system-generated constraint names by constraint type. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different.	Sofware measure	system catalog base tables only	2023-01-10 14:52	MIT License	View
338	Too generic names (table constraints)	Find table constraints (constraints that are associated directly with the table) that have too generic names like "key" or the name contain too generic words like "data" (all constraints restrict data in the table), or the name is an abbreviation of a constraint type name.	Problem detection	system catalog base tables only	2023-01-08 10:40	MIT License	View
339	Too generic names (unique indexes)	Find unique indexes that have too generic names like "key" or the name contain too generic words like "data" (all constraints restrict data in the table), or the name is an abbreviation of a constraint type name.	Problem detection	system catalog base tables only	2023-01-08 10:38	MIT License	View
340	Too generic names (routines)	"Avoid using the same word for two purposes. Using the same term for two different ideas is essentially a pun" (Robert C. Martin, Clean Code) Do not use the Estonian words like 'lisa', 'muuda', 'kustuta' or the corresponding English words 'add', 'delete', 'update' as the names of routines because it would make the code much less understandable. What is the task of the routine f_add? Register people? Register orders? What? Do not refer to the generic concepts like data and information because all routines work with these.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-07 20:38	MIT License	View