search

perseas / Pyrseas

Provides utilities for Postgres database schema versioning.
https://perseas.github.io/
BSD 3-Clause "New" or "Revised" License
395 stars 67 forks source link

Add support column specification GENERATED AS IDENTITY #164

Closed jmafc closed 6 years ago

jmafc commented 7 years ago

Coming in PG 10. See https://www.postgresql.org/docs/devel/static/sql-createtable.html

jmafc commented 6 years ago

PG 10's pg_dump behaves differently with IDENTITY columns than the old SERIAL "type". In the latter case, it creates the table first, then the sequence, then it issues an ALTER SEQUENCE seqname OWNED BY table.column and then an ALTER TABLE SET DEFAULT nextval. With IDENTITY columns, after creating the table it manages to wrap everything up with an

ALTER TABLE table ALTER COLUMN column ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME seqname
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

IOW, although the info in the catalogs for both sequences is the same, it seems to use the ALTER to not only alter the column info but also to create a sequence and attach it to the table.column. Haven't found yet whether it relies on something other than pg_attribute.attidentity to trigger this combined behavior.