How to get information about the sequence behind the new Identity Column in Postgres 10? (GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY)

Question

Postgres 10 brings an implementation of the SQL standard GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY feature, loosely known as identity column. This feature supplants Postgres’ own SERIAL pseudo-type, eliminating its issues/problems. For more info, see here and here.

One of the main reasons for GENERATED… being superior to SERIAL is that the sequence number generator is handled in the background transparently. We can add the column, adjust the current sequence number, and drop the column, all without referring explicitly to the sequence.

CREATE TABLE tbl (
    col  BIGINT 
         GENERATED ALWAYS AS IDENTITY 
         PRIMARY KEY ,
    col2 text
) 
;

And…

ALTER TABLE tbl
ALTER COLUMN col
RESTART WITH 1000
;

And…

ALTER TABLE tbl
ALTER COLUMN col
DROP IDENTITY IF EXISTS
;

Or…

ALTER TABLE tbl
ALTER COLUMN col
DROP COLUMN IF EXISTS
;

Note how in none of that example code did we refer to a specific sequence-generating object.

I presume this feature is implemented using the existing Sequence feature in Postgres.

➠ How do we query this background sequence? How do we ask what the last/next value generated will be? What is the equivalent of the Sequence manipulation functions?

Answer 1

It appears you use the same functions as before:

https://www.postgresql.org/docs/10/static/functions-info.html

pg_get_serial_sequence returns the name of the sequence associated with a column, or NULL if no sequence is associated with the column. If the column is an identity column, the associated sequence is the sequence internally created for the identity column. For columns created using one of the serial types (serial, smallserial, bigserial), it is the sequence created for that serial column definition. In the latter case, this association can be modified or removed with ALTER SEQUENCE OWNED BY. (The function probably should have been called pg_get_owned_sequence; its current name reflects the fact that it has typically been used with serial or bigserial columns.) The first input parameter is a table name with optional schema, and the second parameter is a column name. Because the first parameter is potentially a schema and table, it is not treated as a double-quoted identifier, meaning it is lower cased by default, while the second parameter, being just a column name, is treated as double-quoted and has its case preserved. The function returns a value suitably formatted for passing to sequence functions (see Section 9.16). A typical use is in reading the current value of a sequence for an identity or serial column, for example:

SELECT currval(pg_get_serial_sequence('sometable', 'id'));