Re: Getting the currently used sequence for a SERIAL column

Hanne Moa schrieb am 19.10.2016 um 09:06:
>> regression=# create table t1 (f1 serial);
>> CREATE TABLE
>> regression=# select * from pg_depend where objid = 't1_f1_seq'::regclass or refobjid = 't1_f1_seq'::regclass;
>> classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
>> ---------+-------+----------+------------+----------+-------------+---------
>> 1247 | 47198 | 0 | 1259 | 47197 | 0 | i
>> 1259 | 47197 | 0 | 2615 | 2200 | 0 | n
>> 2604 | 47202 | 0 | 1259 | 47197 | 0 | n
>> 1259 | 47197 | 0 | 1259 | 47199 | 1 | a
>> (4 rows)
>
> This seems to assume that I already know the name of the sequence?
> Looking at the sequence names that's already in use, I cannot safely
> assume anything about the format of their names. I start out knowing
> only the table and column, and I need a string with the sequence name to
> pass on to whatever wants it.

You can use the following statement to find the sequences that a table uses:

select sn.nspname as sequence_schema, s.relname as sequence_name
from pg_class s
join pg_namespace sn on sn.oid = s.relnamespace
join pg_depend d on d.refobjid = s.oid and d.refclassid='pg_class'::regclass
join pg_attrdef ad on ad.oid = d.objid and d.classid = 'pg_attrdef'::regclass
join pg_attribute col on col.attrelid = ad.adrelid and col.attnum = ad.adnum
join pg_class tbl on tbl.oid = ad.adrelid
join pg_namespace n on n.oid = tbl.relnamespace
where s.relkind = 'S'
and d.deptype in ('a', 'n')
and n.nspname = 'public'
and tbl.relname = 'foo'

Of course you can do that for multiple tables as well:

and (n.nspname, t.relname) in ( ('public', 'foo'), ('public'), ('bar') )

I am not entirely sure if that is the "shortest way" to do it, but it works for me.