How to change definition of a Postgres GENERATED (stored) column

Question

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.

Answer 1

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

Answer 2

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:

• https://git.postgresql.org/pg/commitdiff/5d06e99a3cfc23bbc217b4d78b8c070ad52f720e
• https://www.depesz.com/2024/01/15/waiting-for-postgresql-17-alter-table-command-to-change-generation-expression/

---

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.

Answer 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

Answer 4

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:

2. Using SQL

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