Add auto increment with identity option for Postgres

[JoeyMckenzie]

After scouring issues and the docs, there does not seem to be an option for auto-incrementing PK identity columns. Take for example the following table:

CREATE TABLE todos
(
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    description VARCHAR NOT NULL DEFAULT '',
    completed   BOOL    NOT NULL DEFAULT FALSE,
    user_id     INTEGER NOT NULL
);

Once applied, running atlas schema inspect -d "postgres://some:user@someserver/somedatabase" returns the following HCL:

table "todos" {
  schema = schema.public
  column "id" {
    null = false
    type = bigint
  }
  column "description" {
    null    = false
    type    = character_varying
    default = ""
  }
  column "completed" {
    null    = false
    type    = boolean
    default = false
  }
  column "user_id" {
    null = true
    type = integer
  }
  primary_key {
    columns = [table.todos.column.id, ]
  }
}
schema "public" {
}

Dropping the table and re-applying the schema via an atlas schema apply re-creates the table, but without an auto-incrementing ID. Looking at the generated DDL:

-- auto-generated definition via DataGrip
create table todos
(
    id          bigint                                not null
        primary key,
    description varchar default ''::character varying not null,
    completed   boolean default false                 not null,
    user_id     integer
);

Inserts now have to be written in the form:

INSERT INTO todos (id, description, completed, user_id)
VALUES ((SELECT MAX(id) + 1 FROM todos), 'add auto-increment for postgres', false, 1)
-- Or find the last value in a manually defined sequence for the PK column

There seems to be an auto-incrementing option for MySQL, any plans for bringing that over for Postgres?

[a8m]

Hey @JoeyMckenzie and thanks for opening this issue.
Actually, both AUTO_INCREMENT (MySQL and SQLite) and IDENTITY (PostgreSQL) are supported by the core engine (inspect, diff and plan/apply). However, we didn't connect it yet to the atlas DDL (HCL document). I plan to support this later this week. There are additional parts that are missing in atlas DDL.

[JoeyMckenzie]

Good to know, was reading through past PRs and saw that the functionality is there, just hasn't been hooked up to DDL yet. Thank you, eagerly awaiting this release!

[kilianstallz]

Would really appreciate this feature for my current project!

[a8m]

Hey all!
The issue was resolved. Please update to the latest release and see the docs for usage - https://atlasgo.io/ddl/sql#auto-increment

Closing ❤️