Postgresql - SQL query to list all sequences in database

Question

I would like to select all sequences in the database, get the schema of sequence, dependent table, the schema of a table, dependent column.

I've tried the following query:

  SELECT 
    ns.nspname AS sequence_schema_name, 
    s.relname AS sequence_name, 
    t_ns.nspname AS table_schema_name, 
    t.relname AS table_name, 
    a.attname AS column_name,
    s.oid,
    s.relnamespace,
    d.*,
    a.*
  FROM pg_class s
  JOIN pg_namespace ns 
  ON ns.oid = s.relnamespace
  left JOIN pg_depend d --
  ON d.objid = s.oid --TO FIX???
    AND d.classid = 'pg_class'::regclass --TO FIX???
    AND d.refclassid = 'pg_class'::regclass --TO FIX???
  left JOIN pg_class t 
  ON t.oid = d.refobjid --TO FIX???
  left JOIN pg_attribute a 
  ON a.attrelid = d.refobjid 
     AND a.attnum = d.refobjsubid
  left JOIN pg_namespace t_ns 
  ON t.relnamespace = t_ns.oid
  WHERE s.relkind = 'S' 
;

Unfortunately, this query does not work at 100%. The query filter some sequences.

I need it for further processing (after data restore on different ENV, I need to find max column-value and set sequence to MAX+1).

Could anyone help me?

Answer 1

The following query should to work:

create table foo(id serial, v integer);
create table boo(id_boo serial, v integer);
create sequence omega;
create table bubu(id integer default nextval('omega'), v integer);

select sn.nspname as seq_schema,
       s.relname as seqname,
       st.nspname as tableschema,
       t.relname as tablename,
       at.attname as columname
  from pg_class s
  join pg_namespace sn on sn.oid = s.relnamespace
  join pg_depend d on d.refobjid = s.oid 
  join pg_attrdef a on d.objid = a.oid
  join pg_attribute at on at.attrelid = a.adrelid and at.attnum = a.adnum
  join pg_class t on t.oid = a.adrelid
  join pg_namespace st on st.oid = t.relnamespace
 where s.relkind = 'S'
   and d.classid = 'pg_attrdef'::regclass
   and d.refclassid = 'pg_class'::regclass;
┌────────────┬────────────────┬─────────────┬───────────┬───────────┐
│ seq_schema │    seqname     │ tableschema │ tablename │ columname │
╞════════════╪════════════════╪═════════════╪═══════════╪═══════════╡
│ public     │ foo_id_seq     │ public      │ foo       │ id        │
│ public     │ boo_id_boo_seq │ public      │ boo       │ id_boo    │
│ public     │ omega          │ public      │ bubu      │ id        │
└────────────┴────────────────┴─────────────┴───────────┴───────────┘
(3 rows)

For calling sequence related functions you can use s.oid column. For this case, it is sequence unique oid identifier. You need cast it to regclass.

A script for you request can looks like:

do $$
declare
  r record;
  max_val bigint;
begin
  for r in
    select s.oid as seqoid,
           at.attname as colname,
           a.adrelid as reloid
      from pg_class s
      join pg_namespace sn on sn.oid = s.relnamespace
      join pg_depend d on d.refobjid = s.oid 
      join pg_attrdef a on d.objid = a.oid
      join pg_attribute at on at.attrelid = a.adrelid and at.attnum = a.adnum
     where s.relkind = 'S'
       and d.classid = 'pg_attrdef'::regclass 
       and d.refclassid = 'pg_class'::regclass
  loop
    -- probably lock here can be safer, in safe (single user) maintainance mode
    -- it is not necessary
    execute format('lock table %s in exclusive mode', r.reloid::regclass);

    -- expect usual one sequnce per table
    execute format('select COALESCE(max(%I),0) from %s', r.colname, r.reloid::regclass)
       into max_val;

    -- set sequence
    perform setval(r.seqoid, max_val + 1);
  end loop;
end;
$$

Note: Using %s for table name or sequence name in format function is safe, because the cast from Oid type to regclass type generate safe string (schema is used when it is necessary every time, escaping is used when it is needed every time).