Columns

Column definitions: identity, generated, compression, storage, statistics, and the COLUMN reference block.

Column definitions live in the ( ) list (Part 1). Per-column attributes that PostgreSQL expresses as ALTER TABLE ... ALTER COLUMN statements go in the COLUMN name { } block inside the table’s { } (Part 2).

Standard column types

TABLE products (
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    sku         TEXT    NOT NULL,
    price       NUMERIC(10,2) NOT NULL DEFAULT 0,
    tags        TEXT[]  NOT NULL DEFAULT '{}',
    metadata    JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT pk_products PRIMARY KEY (id)
);

-- emits
CREATE TABLE "public"."products" (
    "id"         bigint GENERATED ALWAYS AS IDENTITY,
    "sku"        text NOT NULL,
    "price"      numeric(10,2) NOT NULL DEFAULT 0,
    "tags"       text[] NOT NULL DEFAULT '{}',
    "metadata"   jsonb,
    "created_at" timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT "pk_products" PRIMARY KEY ("id")
);

GENERATED ALWAYS AS (computed column)

TABLE order_items (
    quantity   INTEGER NOT NULL,
    unit_price NUMERIC(10,2) NOT NULL,
    total      NUMERIC(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    CONSTRAINT pk_order_items PRIMARY KEY (order_item_id)
);

-- emits
CREATE TABLE "public"."order_items" (
    "quantity"   integer NOT NULL,
    "unit_price" numeric(10,2) NOT NULL,
    "total"      numeric(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    CONSTRAINT "pk_order_items" PRIMARY KEY ("order_item_id")
);

GENERATED … AS IDENTITY (sequence-backed)

TABLE users (
    id_always BIGINT GENERATED ALWAYS AS IDENTITY,
    id_default BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1000 INCREMENT BY 1),
    CONSTRAINT pk PRIMARY KEY (id_always)
);

-- emits
CREATE TABLE "public"."users" (
    "id_always"  bigint GENERATED ALWAYS AS IDENTITY,
    "id_default" bigint GENERATED BY DEFAULT AS IDENTITY (START WITH 1000 INCREMENT BY 1),
    CONSTRAINT "pk" PRIMARY KEY ("id_always")
);

COLUMN reference block

COLUMN name { } inside the table’s { } block holds attributes that PG expresses as ALTER TABLE ALTER COLUMN statements.

TABLE users (
    email      TEXT NOT NULL,
    status     user_status NOT NULL,
    payload    JSONB,
    notes      TEXT,
    CONSTRAINT pk_users PRIMARY KEY (id)
)
{
    COLUMN email {
        COMMENT    "Verified email address";
        STATISTICS 300;
        GRANTS     { SELECT TO reporting_role; }
    }

    COLUMN status {
        STATISTICS 500;
    }

    COLUMN payload {
        COMPRESSION lz4;
        STORAGE     extended;
    }

    COLUMN notes {
        STATISTICS 0;
    }
}

-- emits
COMMENT ON COLUMN "public"."users"."email" IS 'Verified email address';
ALTER TABLE "public"."users" ALTER COLUMN "email"   SET STATISTICS 300;
ALTER TABLE "public"."users" ALTER COLUMN "status"  SET STATISTICS 500;
ALTER TABLE "public"."users" ALTER COLUMN "payload" SET COMPRESSION lz4;
ALTER TABLE "public"."users" ALTER COLUMN "payload" SET STORAGE extended;
ALTER TABLE "public"."users" ALTER COLUMN "notes"   SET STATISTICS 0;
GRANT SELECT ("email") ON TABLE "public"."users" TO "reporting_role";

Full COLUMN block attribute reference

Attribute	Emits
`COMMENT 'text'`	`COMMENT ON COLUMN t.c IS '...'`
`STATISTICS n`	`ALTER TABLE t ALTER COLUMN c SET STATISTICS n`
`COMPRESSION method`	`ALTER TABLE t ALTER COLUMN c SET COMPRESSION m`
`STORAGE type`	`ALTER TABLE t ALTER COLUMN c SET STORAGE s`
`DEPRECATED "msg"`	`COMMENT ON COLUMN t.c IS '[DEPRECATED] msg'`
`RENAMED FROM old`	`ALTER TABLE t RENAME COLUMN old TO new`
`GRANTS { }`	`GRANT privilege (col) ON TABLE t TO role`
`REVOKES { }`	`REVOKE privilege (col) ON TABLE t FROM role`

Column rename

The new name goes in the ( ) list and the COLUMN block. The old name appears only in RENAMED FROM.

TABLE users (
    email_address TEXT NOT NULL,
    CONSTRAINT uq_users_email UNIQUE (email_address)   -- must use new name
)
{
    COLUMN email_address {
        RENAMED FROM email;
        COMMENT 'Verified email address';
    }
}

-- emits
ALTER TABLE "public"."users" RENAME COLUMN "email" TO "email_address";
COMMENT ON COLUMN "public"."users"."email_address" IS 'Verified email address';

After the migration, remove RENAMED FROM email from source. See Lifecycle Directives for full rename rules.