postgresqlpostgresql-12generated

How to convert PostgreSQL 12 generated column to a normal column?

I have a generated column in PostgreSQL 12 defined as

create table people (
   id bigserial primary key,
   a varchar,
   b boolean generated always as (a is not null) stored
);

but now i want column b to be settable but i don't want to lose the data already in the column, i could drop the column and recreate it but that would lose the current data.

Thanks In Advance

Solution

  • You can run several ALTER TABLE statements in a transaction:

    BEGIN;
ALTER TABLE people ADD b_new boolean;
UPDATE people SET b_new = b;
ALTER TABLE people DROP b;
ALTER TABLE people RENAME b_new TO b;
COMMIT;