15

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
    Commented Oct 11, 2019 at 16:37

4 Answers 4

13

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

Postgres >= 17

Postgres 17 adds a new ALTER TABLE subcommand, namely ALTER COLUMN ... SET EXPRESSION

so I imagine we will be able to:

ALTER TABLE test ALTER COLUMN val_sum SET EXPRESSION AS (val_a + val_b + 1) ;

Related reading:


Pre postgres 17 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

Caveat: Postgres tables have a limit of 1600 columns, dropped columns still count towards this limit. If you hit the limit you'll need to either drop and recreate the table, or use a tool like https://reorg.github.io/pg_repack/ clean up the table for you.

1
  • pg_repack preserves dropped columns. It replaces the values using NULL, but attisdropped remains in the system catalog. It is safer (and more future proof) to replace the relfilenode with a new one without touching other metadata than to maintain the correctness of rewriting all associated metadata. (I am a contributor to pg_repack github.com/reorg/pg_repack/blob/ver_1.5.0/lib/… )
    – Melkij
    Commented Jan 22 at 12:56
3

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

2

There are two ways to do it in PostgreSQL 14:

  1. Using PGAdmin 4: right click on table, then Properties, then click the pencil icon next to the identity column you want to change:

enter image description here

  1. Using SQL
ALTER TABLE IF EXISTS public.parameter_global_config
 ALTER COLUMN parameter_global_config_id SET GENERATED BY DEFAULT;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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