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	...
401	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
402	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
403	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
404	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
405	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
406	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
407	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
408	Too generic names (tables)	Find the names of tables that are too generic. In SQL databases data/information is represented as values in columns. It is not a good style to use generic names like table, data, information, list etc. in the names of tables.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-07 20:37	MIT License	View
409	Too generic names (unique index columns)	Find unique index (not associated with a constraint) columns with the names like id, identifikaator, code, kood, number, etc. The names could have underscores as the prefix or suffix. These are too generic names.	Problem detection	system catalog base tables only	2023-01-07 20:34	MIT License	View
410	Do not use approach that one size fits all (unique index columns)	Find base base tables have a simple unique index (not associated with a constraint) that contains the column with the (case insensitive) name id and an integer type. In addition, the key values are generated automatically by the system by using a sequence generator.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-07 15:12	MIT License	View
411	Parameter name contains the routine name	Find parameters that have the same name as the routine. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-06 18:03	MIT License	View
412	Parameter name is the same as the routine name	Find parameters that have the same name as the routine. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-06 18:03	MIT License	View
413	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
414	Validation method does not confirm	Find user-defined SQL and PL/pgSQL routines that do not return a value although the name suggest that it should return a value (starts with "validate" or "check").	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-06 14:25	MIT License	View
415	A getter does not return a value	Find user-defined SQL and PL/pgSQL routines that do not return a value although the name suggest that it should return a value (starts with "get").	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-01-06 14:24	MIT License	View
416	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
417	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
418	One-to-one relationships	Find one-to-one relationships between base tables. In this case the foreign key columns must have primary key or unique constraint. These tables could implement inheritance hierarchy that has been specified in the conceptual data model.	General	system catalog base tables only	2023-01-06 13:39	MIT License	View
419	Column name contains the table name	Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-06 12:38	MIT License	View
420	Column name is the same as the table name	Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Sometimes columns with such names are used as the key columns. Make sure that the naming style is consistent.	General	INFORMATION_SCHEMA+system catalog base tables	2023-01-06 12:38	MIT License	View