node.jspostgresqldatabase-migrationdbmigratenode-pg-migrate

How to define "INT GENERATED ALWAYS AS IDENTITY" column with node-pg-migrate?


How do I define a migration file using node-pg-migrate for the below table

CREATE TABLE color (
    color_id INT GENERATED ALWAYS AS IDENTITY,
    color_name VARCHAR NOT NULL
);

There seems to be no documentation on how to do INT GENERATED ALWAYS AS INDENTITY column using node-pg-migrate


Solution

  • I haven't checked the history or when sequenceGenerated has been added, but at least at the time of writing the SQL you seek for can be generated with

    pgm.createTable(
      'color',
      {
        color_id: {
          type: 'integer',
          sequenceGenerated: {
            precedence: 'ALWAYS'
          }
        },
        color_name: {
          type: 'varchar',
          notNull: true
        }
      }
    )
    

    This will create a migration with SQL

    CREATE TABLE "color" (
      "color_id" integer GENERATED ALWAYS AS IDENTITY,
      "color_name" varchar NOT NULL
    );