postgresql

In PostgreSQL, How to find which table uses specific Sequence?


I have a sequence called seque_post.

I need to find out in what table it's being used. Is there a way to write a query that will give the table name?

I wrote this query to find the sequence:

select *
from pg_class
where relname like 'seque_post'

there is a filed there reltoastrelid which according to the manual gives:

OID of the TOAST table associated with this table, 0 if none. The TOAST table stores large attributes "out of line" in a secondary table.

but i'm not sure how to continue from here.. suggestions?


Solution

  • To find the table a sequence is "related" to, you can use something like this:

    select seq_ns.nspname as sequence_schema, 
           seq.relname as sequence_name,
           tab_ns.nspname as table_schema,
           tab.relname as related_table
    from pg_class seq
      join pg_namespace seq_ns on seq.relnamespace = seq_ns.oid
      JOIN pg_depend d ON d.objid = seq.oid AND d.deptype = 'a' 
      JOIN pg_class tab ON d.objid = seq.oid AND d.refobjid = tab.oid
      JOIN pg_namespace tab_ns on tab.relnamespace = tab_ns.oid
    where seq.relkind = 'S' 
      and seq.relname = '[your sequence name]'
      and seq_ns.nspname = 'public';
    

    Just to complete the picture:

    The other way round (looking up a sequence for a column) is easier, because Postgres has a function to find the sequence for a column:

    select pg_get_serial_sequence('public.some_table', 'some_column');