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

Question

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

Answer 1

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;

Answer 2

1. alter table people add column temp_data boolean;
2. update people set temp_data=b --(copy data from column b to temp_data)
3. Do whatever you want with column "b".
4. update people set b=temp_data --(move data back)
5. alter table people drop column temp_data --(optional)