10

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.

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

3 Answers 3

7

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
3
  • 2
    The OP is trying to modify the generation formula, I believe.
    – mustaccio
    Oct 11, 2019 at 17:09
  • 1
    Yes, the answer according the docs (IMHO) is no, you cannot alter a computed column.
    – McNets
    Oct 11, 2019 at 17:10
  • Thanks for clarification. Marking this as the answer, at least until somebody posts a solution.
    – Marius
    Oct 12, 2019 at 11:21
3

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

2

Caveats

This only works if you're not changing the type of the generated column. Also it does not work if you're changing what columns you depend on. (So, to be clear, it won't help you in the specific example given)

Depend on a function instead

Create a function, then generate the column using that function. That way, you can update the generated column definition without any ALTER statements. This can be very handy if you've got views depending on the table, as those otherwise would need to be dropped and re-created.

Example

CREATE OR REPLACE FUNCTION do_math(i integer)
 RETURNS integer LANGUAGE sql IMMUTABLE STRICT
AS $function$
SELECT i + 10;
$function$;

CREATE TABLE example_table (
    number integer PRIMARY KEY,
    gen integer GENERATED ALWAYS AS (do_math(number)) STORED
);

INSERT INTO example_table VALUES (1),(4);

SELECT * FROM example_table;
-- =>
--  number | gen 
-- --------+-----
--       1 |  11
--       4 |  14

CREATE OR REPLACE FUNCTION do_math(i integer)
 RETURNS integer LANGUAGE sql IMMUTABLE STRICT
AS $function$
SELECT i * 100;
$function$;

-- Reguired to re-caluclate the genrated columns
UPDATE example_table SET number = number;

SELECT * FROM example_table;
-- => 
--  number | gen 
-- --------+-----
--       1 | 100
--       4 | 400

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.