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?
source to share
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');
source to share
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
source to share
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.
source to share