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