1

How to change definition of a Postgres 12 GENERATED column?

CREATE TABLE test (
    id serial PRIMARY KEY,
    val_a int4 NOT NULL DEFAULT 0,
    val_b int4 NOT NULL DEFAULT 0,
    val_sum int4 generated always AS (val_a + val_b) stored
);

I tried

ALTER TABLE test ALTER COLUMN val_sum TYPE int4 generated always AS (val_a + val_b + 1) stored;
ALTER TABLE test ALTER COLUMN val_sum SET generated always AS (val_a + val_b + 1) stored;

but both give syntax errors.

The alternative is to drop & add column, which works, but I wonder how to simply change it like any other column.

  • Altering the generation expression would be semantically inconsistent, as values in existing rows would become incompatible with the new expression. – mustaccio Oct 11 '19 at 16:37
2

I'm not sure this is the correct answer, but reading the docs about ALTER TABLE I would say: No, you can't.

where action is one of:

ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]

and column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

It seems that the only command that allows column_constraint is ADD COLUMN

In fact you can add a constraint name to the computed column:

val_sum int4 CONSTRAINT GA_test generated always AS (val_a + val_b) stored
|improve this answer|||||
  • 1
    The OP is trying to modify the generation formula, I believe. – mustaccio Oct 11 '19 at 17:09
  • Yes, the answer according the docs (IMHO) is no, you cannot alter a computed column. – McNets Oct 11 '19 at 17:10
  • Thanks for clarification. Marking this as the answer, at least until somebody posts a solution. – Marius Oct 12 '19 at 11:21
0

For now as a possible work around can you drop the column and add it again?

ALTER TABLE test drop COLUMN val_sum;
ALTER TABLE test add COLUMN val_sum int4 generated always AS (val_a + val_b + 1) stored;

Fiddle

Fiddle with test

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.