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

Laurenz Albe · Accepted Answer · 2021-05-31 14:20:06Z

3

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, 2021 at 14:20

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mitko Keckaroski · Accepted Answer · 2021-05-31 14:22:42Z

1

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)

answered May 31, 2021 at 14:22

Mitko Keckaroski

1,0891 gold badge10 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related