Postgres list all the tables PK’s sequence name

Photo by Flavio Gasperini on Unsplash

All Tables PK / FK / UNIQUE / CHECK

Information_schema.table_constraints contains all tables constraints info, but no column name.

select 
  constraint_schema, 
  constraint_name, 
  table_name, 
  constraint_type
from information_schema.table_constraints
where constraint_type in (
  'PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE', 'CHECK')

All Tables PK / FK / UNIQUE / CHECK with the Column name

Information_schema.key_column_usage contains all column usage info about PRIMARY_KEY, FOREIGN_KEY, UNIQUE, CHECK, also provides the Column name.

select 
  tc.table_schema, 
  tc.table_name, 
  tc.constraint_name, 
  kcu.column_name, 
  kcu.ordinal_position as column_position
from information_schema.table_constraints tc
join information_schema.key_column_usage kcu
  on kcu.constraint_name = tc.constraint_name
where tc.constraint_type in (
  'PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE', 'CHECK');

All Tables PK column sequence

With pg_get_serial_sequence method, returns the name of the sequence associated with a column.

select pg_get_serial_sequence('table_name', 'pk_name') as seq_name

So we can list all the tables PK’s sequence name.

select *
from (
  select
    tc.table_name,
    kcu.column_name,
    pg_get_serial_sequence(tc.table_name, kcu.column_name) as pk_seq
  from information_schema.table_constraints tc
  join information_schema.key_column_usage kcu
    on kcu.constraint_name = tc.constraint_name
  where tc.constraint_type = 'PRIMARY KEY'
) as meta
where meta.pk_seq is not null;

with sequence current value.

select *
  from (
    select
      tc.table_name,
      kcu.column_name,
      pg_get_serial_sequence(tc.table_name, kcu.column_name) as pk_seq,
      currval(pg_get_serial_sequence(tc.table_name, kcu.column_name))
    from information_schema.table_constraints tc
    join information_schema.key_column_usage kcu
      on kcu.constraint_name = tc.constraint_name
    where tc.constraint_type = 'PRIMARY KEY'
) as meta
where meta.pk_seq is not null;