Query goal: | Find all the primary key and unique constraints of base tables. |
Notes about the query: | In SQL a key is an ordered set of columns that must contain at least one column. The query result preserves the order of columns in the key. The query does not take into account uniqueness constraints that have been implemented as unique indexes, instead of declaring a unique constraint. PostgreSQL 14 added primary keys, unique constraints, and foreign keys to system catalogs. Thus the query was modified to exclude the results from the system catalog. PostgreSQL 14 added primary keys, unique constraints, and foreign keys to system catalogs. Thus the query was modified to exclude the results from the system catalog. |
Query type: | General (Overview of some aspect of the database.) |
Query license: | MIT License |
Data source: | system catalog only |
SQL query: | Click on query to copy it
with keys as (select o.conname, nc.nspname as key_schema, c.relname as key_table, c.oid as key_table_oid, o.conkey AS key_col, case when o.contype='p' then 'PRIMARY KEY' ELSE 'UNIQUE' END as contype from pg_constraint o inner join pg_class c on c.oid = o.conrelid inner join pg_namespace as nc on nc.oid=c.relnamespace inner join pg_authid AS a on nc.nspowner=a.oid where (nc.nspname='public' or rolname<>'postgres') and o.contype in ('u', 'p') ), keys_unnest as (select conname, key_schema, key_table, key_table_oid, key_col, key_col_num, ordin, contype from keys, unnest(keys.key_col) with ordinality as k(key_col_num, ordin)), keys_with_names as (select conname, key_schema, key_table, contype, array_agg(a_key.attname order by ordin) as key_col from keys_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false group by conname, key_schema, key_table, contype) select conname, key_schema, key_table, contype, key_col from keys_with_names order by key_schema, key_table, contype, key_col; |
Collection name | Collection description |
---|---|
Find problems by overview | Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not . |
Category name | Category description |
---|---|
Uniqueness | Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes. |
Validity and completeness | Queries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness). |