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-10-18 08:45:52
Message-ID: 611da0cd-6baa-baf8-d8df-eebec73ab1a2@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

Until now we've 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 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Kellerer 2016-10-18 09:12:10 Re: Getting the currently used sequence for a SERIAL column
Previous Message Condor 2016-10-18 07:30:40 Re: Dump all the indexes/constraints/roles