Surrogate key columns

with keys as (select 
(select nspname from pg_namespace where oid=c.relnamespace) as key_schema,
c.relname as key_table, 
c.oid as key_table_oid,
o.conkey AS key_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('u', 'p') and cardinality(o.conkey)=1),
keys_unnest as (select key_schema, key_table, key_table_oid, key_col, key_col_num, ordin
from keys, unnest(keys.key_col) with ordinality as k(key_col_num, ordin)),
keys_with_names AS (select key_schema, key_table, a_key.attname 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
),
fk as (select 
(select nspname from pg_namespace where oid=c.relnamespace) as key_schema,
c.relname as key_table, 
c.oid as key_table_oid,
o.conkey AS key_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype='f'),
fk_unnest as (select key_schema, key_table, key_table_oid, key_col, key_col_num, ordin
from fk, unnest(fk.key_col) with ordinality as k(key_col_num, ordin)),
fk_with_names AS (select key_schema, key_table, a_key.attname as key_col
from fk_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),
key_not_fk AS (select key_schema, key_table, key_col
from keys_with_names
except select key_schema, key_table, key_col
from fk_with_names)
SELECT c.table_schema, c.table_name , c.column_name, c.data_type, coalesce (c.column_default, d.domain_default)  AS column_default, c.is_identity
FROM information_schema.columns c
LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
INNER JOIN information_schema.schemata s
ON c.table_schema=s.schema_name
WHERE  (coalesce (c.column_default, d.domain_default) ILIKE '%nextval%'  OR c.is_identity='YES')
AND c.data_type IN ('smallint','integer','bigint')
AND (c.table_schema = 'public'
OR s.schema_owner<>'postgres')
AND EXISTS (SELECT *
FROM key_not_fk AS k
WHERE k.key_schema=c.table_schema
AND k.key_table=c.table_name
AND k.key_col=c.column_name)
ORDER BY table_schema, table_name;

Query goal:	Find surrogate keys. Surrogate key is a key that consist of one column, which has an integer type. The key has been declared by using PRIMARY KEY or UNIQUE constraint. The column is associated with a sequence generator (either external or internal, i.e., created by the system automatically because the column has been declared as an identity column). The column does not participate in any foreign key.
Notes about the query:	The query assumes that surrogate key values are generated by using an external or internal sequence generator. The query takes into account a possibility that a column may be associated with a sequence generator through a domain. The query does not check as to whether the column has an integer type and as to whether the column has been declared mandatory.
Query type:	General (Overview of some aspect of the database.)
Query license:	MIT License
Data source:	INFORMATION_SCHEMA+system catalog
SQL query:	Click on query to copy it with keys as (select (select nspname from pg_namespace where oid=c.relnamespace) as key_schema, c.relname as key_table, c.oid as key_table_oid, o.conkey AS key_col from pg_constraint o inner join pg_class c on c.oid = o.conrelid where o.contype in ('u', 'p') and cardinality(o.conkey)=1), keys_unnest as (select key_schema, key_table, key_table_oid, key_col, key_col_num, ordin from keys, unnest(keys.key_col) with ordinality as k(key_col_num, ordin)), keys_with_names AS (select key_schema, key_table, a_key.attname 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 ), fk as (select (select nspname from pg_namespace where oid=c.relnamespace) as key_schema, c.relname as key_table, c.oid as key_table_oid, o.conkey AS key_col from pg_constraint o inner join pg_class c on c.oid = o.conrelid where o.contype='f'), fk_unnest as (select key_schema, key_table, key_table_oid, key_col, key_col_num, ordin from fk, unnest(fk.key_col) with ordinality as k(key_col_num, ordin)), fk_with_names AS (select key_schema, key_table, a_key.attname as key_col from fk_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), key_not_fk AS (select key_schema, key_table, key_col from keys_with_names except select key_schema, key_table, key_col from fk_with_names) SELECT c.table_schema, c.table_name , c.column_name, c.data_type, coalesce (c.column_default, d.domain_default) AS column_default, c.is_identity FROM information_schema.columns c LEFT JOIN information_schema.domains d USING (domain_schema, domain_name) INNER JOIN information_schema.schemata s ON c.table_schema=s.schema_name WHERE (coalesce (c.column_default, d.domain_default) ILIKE '%nextval%' OR c.is_identity='YES') AND c.data_type IN ('smallint','integer','bigint') AND (c.table_schema = 'public' OR s.schema_owner<>'postgres') AND EXISTS (SELECT * FROM key_not_fk AS k WHERE k.key_schema=c.table_schema AND k.key_table=c.table_name AND k.key_col=c.column_name) ORDER BY table_schema, table_name;

Category name	Category description
Comfortability of data management	Queries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient.
Sequence generators	Queries of this category provide information about sequence generators and their usage.
Uniqueness	Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.

Surrogate key columns

Collections where the query belongs to

Categories where the query belongs to

Reference materials for further reading