Getting the currently used sequence for a SERIAL column

From: Hanne Moa <hanne(dot)moa(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Getting the currently used sequence for a SERIAL column
Date: 2016-09-29 13:10:52
Message-ID: CACQ=rrcXjTqPUSgdyqxM93f2OuBoYT618796W5jgocxEK06CrQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

we have up until now, been using pg_get_serial_sequence() to discover
which sequence is in use, but can no longer do so due to two tables
needing to share the same sequence (prior to being properly merged. No
duplicate values, luckily). For one of the tables,
pg_get_serial_sequence() won't be returning anything useful since it
tracks which table *owns* a sequence and not which sequence is used by
which table.column.

The necessary information seems to be in the table
"information_schema.columns", in "column_default". Is this to be
regarded as internal API or is it safe to use this to find the correct
sequence? It works in all cases and on all the version of postgres
that are relevant to us. The production system is currently running
9.3 (I'm pining for 9.5...)

Furthermore, what's stored in the column seems to be a string of the
format "nextval('sequencename'::regclass)". Is there a function to
parse this, to return just the sequence name, or will the sequence
name always be without for instance a schema name so that a naive
parser of our own will do? Googling found no candidates.

HM

Browse pgsql-general by date

  From Date Subject
Next Message Adam Brusselback 2016-09-29 13:37:17 Re: Incrementally refreshed materialized view
Previous Message Nguyễn Trần Quốc Vinh 2016-09-29 12:10:37 Re: Incrementally refreshed materialized view