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?

This may depend how thorough you want to be - a sequence in Postgres can be "owned by" a single table and column (e.g. when created by the pseudo-type Serial or BigSerial), but can be the default for any number of other columns, or just used manually in some function somewhere. — IMSoP
– IMSoP, Commented Jul 21, 2015 at 13:51
TOAST is to do with storing large values (e.g. long strings in a text column) so is not relevant to this question. — IMSoP
– IMSoP, Commented Jul 21, 2015 at 13:52
@IMSoP in my sequences are mostly used as Serial, so if it's being used somewhere it's surly as PK for column. Since I don't know where it's being used I'll need to look up everywhere... But it's more likely to be used as PK for table... regarding the TOAST, as I said I don't really know how to solve this problem.. it was just a wild guess. — John
– John, Commented Jul 21, 2015 at 14:04

dustinevan · Accepted Answer · 2019-08-22 00:16:35Z

26

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

edited Aug 22, 2019 at 0:16

dustinevan

99710 silver badges23 bronze badges

answered Jul 21, 2015 at 14:22

user330315

Sign up to request clarification or add additional context in comments.

10 Comments

IMSoP Over a year ago

I'd just found pg_depend, but my query is much uglier than this. I found myself writing select oid from pg_class where relname='pg_class'; the classid and refclassid columns on that table are weird.

John Over a year ago

I don't understand the 2nd way... I don't know what is the column name nor the table name.. that is what i'm looking for.

IMSoP Over a year ago

@John I think "the other way" was meant to mean "looking up in the other direction". It doesn't apply to your case, but might be useful for other readers.

Nick Barnes Over a year ago

You can modify the second case for a much simpler solution:

SELECT attrelid::regclass, attname FROM pg_attribute WHERE pg_get_serial_sequence(attrelid::regclass::text, attname) = 'public.seque_post'

dustinevan Over a year ago

I suggested an edit so that people don't have to read a bunch to figure out that they should replace 'seque_post' with their own sequence name. nbd though. Thanks a ton for this.

|

IMSoP · Accepted Answer · 2015-07-21 15:11:54Z

4

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

edited Jul 21, 2015 at 15:11

answered Jul 21, 2015 at 14:36

IMSoP

99.7k18 gold badges135 silver badges183 bronze badges

2 Comments

John Over a year ago

it gives error ERROR: schema "filter_rules" does not exist

IMSoP Over a year ago

@John That's just the name of the sequence I was testing with. Edited to clarify.

luba · Accepted Answer · 2015-07-21 15:31:30Z

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.

edited Jul 21, 2015 at 15:31

answered Jul 21, 2015 at 15:17

luba

1224 bronze badges

1 Comment

Yoni Sade Over a year ago

This query gives false dependencies. Missing clauses: AND d.classid = 'pg_catalog.pg_class'::regclass AND d.refclassid = 'pg_catalog.pg_class'::regclass

EAmez · Accepted Answer · 2025-02-04 08:54:30Z

1

Mixing several given answers I've made up this query that I'm using in a code block for updating all of the sequences on my database:

SELECT 
    D.refobjid::regclass AS table_name, -- the table that uses the sequence
    a.attname AS column_name -- the column that uses the sequence
FROM pg_depend AS D 
    JOIN pg_attribute AS a ON a.attrelid = D.refobjid AND a.attnum = D.refobjsubid
WHERE 
    D.classid = 'pg_catalog.pg_class'::regclass 
    AND D.refclassid = 'pg_catalog.pg_class'::regclass
    AND D.objid = ('<your_seq_schema>.<your_seq_name>')::regclass;

edited Feb 4 at 8:54

answered Feb 4 at 7:54

EAmez

9171 gold badge10 silver badges28 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

10 Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

10 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related