PostgreSQL Query Catalog

Find all foreign tables.

Find indexes with less common access methods. Gin indexes are, for instance, used to speed up PostgreSQL's built in full text search.

Find all non-identifying relationships where the foreign key (a set of columns) is a subset of a uniqueness constraint (primary key, unique, or exclude constraint).

This query retrieves a comprehensive list of all PRIMARY KEY and UNIQUE constraints defined on base tables within the database. These constraints are the fundamental mechanisms for enforcing entity integrity and uniqueness. The output provides essential information for schema auditing, documentation generation, and analyzing the data model's key structures. It allows administrators and developers to quickly verify how uniqueness is enforced for each table.

Find all non-identifying relationships where the foreign key (a set of columns) is not a subset of any uniqueness constraint (primary key, unique, or exclude constraint).

Find secondary indexes that have been created in the database.

Find parameters of user-defined routines that have a default value.

Find indexes to a subset of table rows.

Find user-defined rules for rewriting data manipulation language statements. Rules should be used only for the tasks that cannot be achieved in a declarative manner, i.e., for example, by declaring a constraint.

Find all security policies.

Find all sequence generators.

Find pairs of tables that have both a foreign key that references to the other table. Such cycles can involve more than two tables but the query detects only cycles with two tables.

Find all the base tables that serve as supertables in the inheritance hierarchies

Find system-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table).

Find all CHECK constraints (except NOT NULL) that are associated with a base table or a foreign table column. It is useful to enforce as many constraints at database level as possible. In this way one improves data quality as well as gives extra information to the database users (including the DBMS engines, development environments, and applications).

Find all functions that return a set of rows.

Find base tables where all he non-primary key columns are optional. Avoid too many optional columns. You have to be extra careful with NULLs in case of formulating search conditions of data manipulation statements.

Find base tables where all unique keys (sets of columns covered by a unique constraint, or a unique index) have at least one optional column. In this case there can be rows in the table where the values that should identify the row are missing. Because NULL is not a value and is not duplicate of another NULL the, follwing is possible: CREATE TABLE Uniq(a INTEGER NOT NULL,
b INTEGER,
CONSTRAINT ak_uniq UNIQUE (a, b));

INSERT INTO Uniq(a, b) VALUES (1, NULL);
INSERT INTO Uniq(a, b) VALUES (1, NULL);

Find user-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table).

Find all user mappings for foreign servers and tables