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

Question

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?

Answer 1

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');

Answer 2

The key catalog here is the rather versatile pg_depend, which can connect basically any two items of any sort.

The ::regclass cast is a magic trick for converting to and from oids, which allows you to look up something like this, with no joins (but possible ambiguities):

select 
    D.refobjid::regclass, -- the target table name
    D.* -- see docs for meaning of other columns
from 
    pg_depend as D
where 
    -- source is a relation (in this case, a sequence)
    D.classid = 'pg_catalog.pg_class'::regclass 
    -- target is also a relation (in this case, a table)
    and D.refclassid = 'pg_catalog.pg_class'::regclass
    -- source is the sequence you're looking for, fully qualified name
    and D.objid = 'public.seque_post'::regclass 

Answer 3

Try this using pg_depend instead of pg_class:

SELECT d.refobjid::regclass, a.attname
FROM   pg_depend d
JOIN   pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE  d.objid = 'public."seque_post"'::regclass;

If you add the join to pg_attribute then you even have the column name that uses the sequence. The ::regclass cast can be used to magically convert object identifiers to relation names.

Hope that helps.