PostgreSQL Query Catalog

Find foreign key columns that have a default value that is not present in the parent table. Identify default values that cause violations of the referential constraints.

Find foreign key columns that are covered by a simple check constraint, i.e., the constraint involves only one column. Look only constraints that are directly associated with the table, i.e., are not specified through a domain. Perhaps the constraint should be defined on the referenced candidate key column.

Find foreign key columns that are associated with a sequence generator. Foreign key values are selected amongst the values that are registered as corresponding primary key/unique key values. Values in the foreign key columns are not directly generated by the system. These values might be system generated indirectly - generated when a row is added to the primary (parent) table.

Find foreign key columns that do not have an index. Foreign key columns are often used for performing join operations. It is useful to index such columns.

Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency.

Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns while at the same time the referenced table does have the primary key. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency.

Find foreign key constraints that referenced column is optional.

Find foreign key constraints that reference to the columns that are covered by a unique index not a unique key.

Find foreign key constraints that refer to a base table that has at least one subtable in the inheritance hierarchy. Rows of the subtable do not belong to the supertable in terms of checking the referential integrity. Let us assume that there is a table T with a subtable Tsub. Let us also assume that table B has a foreign key that refers to the table T. If a row is inserted into Tsub, then this row cannot be referenced from B.

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."

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.

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.

This query identifies performance inefficiencies caused by a semantic mismatch between function-based index definitions and their usage within derived tables (views, materialized views). PostgreSQL's query optimizer generally requires the expression in a query to strictly match the expression defined in the index to trigger an index scan. This query flags instances where a column is indexed using one function (e.g., upper(col)) but accessed in a view using a different function (e.g., lower(col)). In such cases, the optimizer cannot utilize the pre-calculated index, resulting in suboptimal execution plans (typically sequential scans).

This query identifies performance inefficiencies within user-defined routines caused by a mismatch between the logic in the routine and the definition of function-based indexes. PostgreSQL's query optimizer requires the expression in a query to strictly match the expression defined in the index to trigger an index scan. This query flags instances where a column is indexed using one function (e.g., upper(col)) but is accessed in a routine using a different function (e.g., lower(col)). Consequently, the optimizer fails to utilize the pre-calculated index, forcing a costly sequential scan.

This query identifies user-defined functions that contain restricted Transaction Control Language (TCL) statements, such as BEGIN, COMMIT, ROLLBACK, or SAVEPOINT. In PostgreSQL, functions execute within the context of the calling query's transaction and are strictly prohibited from managing transaction boundaries. Attempting to execute these commands within a function body results in a runtime error. To implement transactional logic (such as committing data in batches), the code must be refactored into a PROCEDURE, which supports transaction management.

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.

Find generated stored columns in PostgreSQL base tables that duplicate other columns in the table.

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.

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.

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.