In PostgreSQL, how do I find which table is using a specific sequence?
I have a sequence called seque_post.
I need to figure out which table it is used in. 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'
is there reltoastrelid
, which according to the manual gives:
The OID of the TOAST table associated with this table, 0 if not. The TOAST table stores large off-row attributes in a secondary table.
but i'm not sure how to proceed here .. suggestions?
To find the table that the sequence is "associated" with, 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 reverse way (finding a sequence for a column) is easier because Postgres has a function to find a sequence for a column:
select pg_get_serial_sequence('public.some_table', 'some_column');
The key directory here is pretty generic pg_depend
, which can include basically any two items of any type.
Casting ::regclass
is a magic trick for converting to and from oid
s, allowing you to search for something like this, without concatenations (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
Try 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 a join to pg_attribute, you even get a column name that uses the sequence. Listing :: regclass cast can be used to magically convert object identifiers to relation names.
Hope it helps.