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	...
361	Full text search columns that have no gin or gist index	Find columns of base tabels and materialized views with the type tsvector that do not have a gin or a gist index. These are the preferred index types for text search.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-07 09:22	MIT License	View
362	Full text search columns with other type of index than gin or gist index	Find columns of base tabels and materialized views with the type tsvector that do not have a gin or a gist index but have another type of index (e.g., b-tree). Gin and Gist are the preferred index types for text search.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-07 09:22	MIT License	View
363	Function in a function-based index of a column is different from the function that is used in the subquery of a derived table	Find cases where the function of a function-based index of a column is different from the function that is used in the query in a derived table based on the column.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
364	Function in a function-based index of a column is different of the function that is used in the query in a routine based on the column	Create appropriate indexes to speed up queries. If you apply a function to a column in a query, then create a function-based index based on the function to the column.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 17:26	MIT License	View
365	Functions that have transactional control	Find functions that contain transactional control statements (BEGIN, START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT) in their body. PostgreSQL does not permit transaction control in functions.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 10:50	MIT License	View
366	Function Upper or Lower is used in an index on a non-textual column	Find function-based indexes that are based on function Upper or Lower but have been defined on a non-textual column. Such indexes support case insensitive search but in case of non-textual columns this does not have a meaning.	Problem detection	system catalog base tables only	2021-02-25 17:30	MIT License	View
367	Generated stored base table columns	Find generated stored columns in PostgreSQL base tables. The support of generated columns was added to PostgreSQL 12. These could be used to improve the performance of queries.	General	INFORMATION_SCHEMA only	2020-11-06 14:51	MIT License	View
368	Generated stored base table columns duplicates another column in the table	Find generated stored columns in PostgreSQL base tables that duplicate other columns in the table.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
369	Generated stored base table columns that expression does not refer to any column	Find generated stored base table columns that expression does not refer to any column of the table. It could be that there will be a constant value in every row in case of this column. The support of generated columns was added to PostgreSQL 12.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:29	MIT License	View
370	Grantable column privileges	Find column privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-07 13:43	MIT License	View
371	Grantable privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers	Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	system catalog base tables only	2024-01-07 13:43	MIT License	View
372	Grantable roles	Find roles that a member can grant to others, i.e., the role has been granted with ADMIN OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	system catalog base tables only	2024-01-07 13:42	MIT License	View
373	Grantable routine privileges	Find routine privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-07 13:43	MIT License	View
374	Grantable table privileges	Find table privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-07 13:43	MIT License	View
375	Grantable usage privileges	Find usage privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-07 13:43	MIT License	View
376	Granted roles	Find membership relations between roles.	General	system catalog base tables only	2024-01-07 13:30	MIT License	View
377	Grantees	Database must be used by users who have minimal set of privileges for performing tasks. The query helps to find out as to whether some user/role other than PUBLIC and a superuser have rights to use tables and routines of the database.	General	INFORMATION_SCHEMA only	2020-11-06 14:51	MIT License	View
378	Gratuitous context in the names of foreign key columns	Find foreign key columns that name contains twice the name of the referenced (primary) table.	Problem detection	system catalog base tables only	2021-02-25 17:29	MIT License	View
379	Gratuitous context in the names of non-foreign key and non-candidate key columns	Find the names on base table columns that are not a part of a candidate key and a foreign key and that contain the name of the table. Exclude very general column names (for instance, nimi, nimetus, kommentaar, kirjeldus, name, comment, description). In case of these using the table name in the column name is not a problem because it simplifies writing the queries based on the tables. In this case one does not have to rename the columns in the query result.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2022-11-15 13:39	MIT License	View
380	Gratuitous context in the names of parameters	Find routine parameter names that contain the routine name. Names of routine parameters shouldn't contain the name of the routine. It makes the names too long. A routine cannot have two parameters with the same name.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View