How to identify the sequences used by a table in postgresql

Question

How to Identify the table and sequence mapping

Select * from information_schema.sequences

Answer 1

This query lists all tables and their associated sequences:

SELECT DISTINCT
  tns.nspname AS table_schema,
  t.relname AS table_name,
  sns.nspname AS sequence_schema,
  s.relname AS sequence_name
FROM
  pg_namespace tns
  JOIN pg_class t ON tns.oid = t.relnamespace
    AND t.relkind IN ('p', 'r')
  JOIN pg_depend d ON t.oid = d.refobjid
  JOIN pg_class s ON d.objid = s.oid and s.relkind = 'S'
  JOIN pg_namespace sns ON s.relnamespace = sns.oid
;

If the column names are also desired, then the following query can be used:

SELECT
  tns.nspname AS table_schema,
  t.relname AS table_name,
  a.attname AS column_name,
  sns.nspname AS sequence_schema,
  s.relname AS sequence_name,
  d.deptype
FROM
  pg_namespace tns
  JOIN pg_class t ON tns.oid = t.relnamespace
    AND t.relkind IN ('p', 'r')
  JOIN pg_attribute a ON t.oid = a.attrelid
    AND NOT a.attisdropped
  JOIN pg_depend d ON t.oid = d.refobjid
    AND d.refobjsubid = a.attnum
  JOIN pg_class s ON d.objid = s.oid and s.relkind = 'S'
  JOIN pg_namespace sns ON s.relnamespace = sns.oid;

Finally, this query returns only the sequences associated with columns in each table's primary key:

SELECT
  tns.nspname AS table_schema,
  t.relname AS table_name,
  a.attname AS column_name,
  sns.nspname AS sequence_schema,
  s.relname AS sequence_name
FROM
  pg_namespace tns
  JOIN pg_class t ON tns.oid = t.relnamespace
    AND t.relkind IN ('p', 'r')
  JOIN pg_index i ON t.oid = i.indrelid
    AND i.indisprimary
  JOIN pg_attribute a ON i.indrelid = a.attrelid
    AND a.attnum = ANY (i.indkey)
  JOIN pg_depend d ON t.oid = d.refobjid
    AND d.refobjsubid = a.attnum
  JOIN pg_class s ON d.objid = s.oid
    AND s.relkind = 'S'
  JOIN pg_namespace sns ON s.relnamespace = sns.oid;

These queries will not include sequences that are only used for column default values defined as DEFAULT NEXTVAL('sequence_name').

I'm not aware of any mechanism that allows the creation of a dependency between a table column and a sequence in different schemas, so the join to get the sequence namespace shouldn't be necessary; however, there's nothing in the system catalogs that precludes the possibility, so I've included the join just in case such cross-schema dependencies are possible.