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

score 2 · Accepted Answer · answered May 31 '21 at 14:20

2

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;

answered May 31 '21 at 14:20

Laurenz Albe

209,280
17
206
263

score 1 · Answer 2 · answered May 31 '21 at 14:22

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

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

2 Answers2