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.