snippetsqlModerate
PostgreSQL: How can I list the tables to which a sequence belongs?
Viewed 0 times
postgresqlcanthetablessequencehowwhichlistbelongs
Problem
I know I can list all sequences with this:
But I need to know by which table this sequence is used.
The reason I want to do this is to find out which sequences of
Is this possible?
Note: My PostgreSQL version is 11.5.
SELECT * FROM information_schema.sequences;But I need to know by which table this sequence is used.
The reason I want to do this is to find out which sequences of
PRIMARY KEYs are not in the default format of table_name_id_seq Is this possible?
Note: My PostgreSQL version is 11.5.
Solution
The information is stored in
pg_depend:SELECT t.oid::regclass AS table_name,
a.attname AS column_name,
s.relname AS sequence_name
FROM pg_class AS t
JOIN pg_attribute AS a
ON a.attrelid = t.oid
JOIN pg_depend AS d
ON d.refobjid = t.oid
AND d.refobjsubid = a.attnum
JOIN pg_class AS s
ON s.oid = d.objid
WHERE d.classid = 'pg_catalog.pg_class'::regclass
AND d.refclassid = 'pg_catalog.pg_class'::regclass
AND d.deptype IN ('i', 'a')
AND t.relkind IN ('r', 'P')
AND s.relkind = 'S';Code Snippets
SELECT t.oid::regclass AS table_name,
a.attname AS column_name,
s.relname AS sequence_name
FROM pg_class AS t
JOIN pg_attribute AS a
ON a.attrelid = t.oid
JOIN pg_depend AS d
ON d.refobjid = t.oid
AND d.refobjsubid = a.attnum
JOIN pg_class AS s
ON s.oid = d.objid
WHERE d.classid = 'pg_catalog.pg_class'::regclass
AND d.refclassid = 'pg_catalog.pg_class'::regclass
AND d.deptype IN ('i', 'a')
AND t.relkind IN ('r', 'P')
AND s.relkind = 'S';Context
StackExchange Database Administrators Q#260975, answer score: 15
Revisions (0)
No revisions yet.