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:	Queries of this category provide information about the foreign table mechanism.
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 13 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	All foreign tables	Find all foreign tables.	General	INFORMATION_SCHEMA only	2020-11-06 15:20	MIT License	View
2	All user mappings	Find all user mappings for foreign servers and tables	General	system catalog base tables only	2020-11-11 17:00	MIT License	View
3	Excessive privileges on databases, schemas, domains, types, languages, foreign data wrappers, and foreign servers	Find excessive privileges on databases, schemas, domains, collations, sequences, foreign data wrappers, and foreign servers that are probably not needed by a typical application.	Problem detection	system catalog base tables only	2021-12-31 14:40	MIT License	View
4	Find all publications	Find publications of tables that have been created in order to enable logical replication.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-18 15:09	MIT License	View
5	Foreign servers without user mappings	Find foreign servers that do not have any associated user mappings. "A user mapping typically encapsulates connection information that a foreign-data wrapper uses together with the information encapsulated by a foreign server to access an external data resource."	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:29	MIT License	View
6	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
7	Insufficient number of user-defined foreign tables	There must be at least n (two in this case) user-defined foreign tables in the database.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
8	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 are not for the owner and not for the systemic user postgres. Make sure that there is the right amount of privileges for each and every relevant user.	General	system catalog base tables only	2021-03-07 20:57	MIT License	View
9	Privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that have been granted to a superuser	Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that have been granted to a superuser. Superuser can do anything in the database and thus does not need the privileges. The result is a sign that perhaps the executed GRANT statements were incorrect (wrong username) or the grantee later got superuser status (that it shouldn't have).	Problem detection	system catalog base tables only	2022-10-21 15:53	MIT License	View
10	Publications with no tables	Find publications that do not contain any table.	Problem detection	system catalog base tables only	2021-02-25 17:30	MIT License	View
11	Unused foreign data wrappers	Find foreign data wrappers that do not have any associated foreign servers.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
12	Unused foreign servers	Find foreign servers that do not have any associated foreign tables.	Problem detection	INFORMATION_SCHEMA only	2021-02-25 17:30	MIT License	View
13	Updataple foreign tables that refer to another PostgreSQL table	Find foreign tables that have been defined based on postgresql_fwd foreign data wrapper and that are updatable. Make sure that updatability is in this case needed.	General	INFORMATION_SCHEMA only	2020-11-06 17:15	MIT License	View