Re: Getting the currently used sequence for a SERIAL column

Hanne Moa <hanne(dot)moa(at)gmail(dot)com> writes:
> 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.

Personally, I'd try looking in pg_depend to see if the column's default
expression has a dependency on a relation of type sequence. That avoids
all the fun of parsing the expression and turns it into a simple SQL
join problem.

regards, tom lane