How to set a normal column back to a generated one in postgresql?

June 22, 2022

I am firstly creating a table like this:

   CREATE TABLE public.case_change (
        id                      integer NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
        to_quantity             integer NOT NULL,
        to_cancelled_quantity   integer NOT NULL DEFAULT 0,
        to_completed_quantity   integer NOT NULL DEFAULT 0,
        to_outstanding_quantity integer GENERATED ALWAYS AS (to_quantity - to_cancelled_quantity - to_completed_quantity) STORED NOT NULL
);

And then I am applying this:

alter table public.case_change alter column to_outstanding_quantity DROP EXPRESSION;
alter table public.case_change alter column to_outstanding_quantity set DEFAULT 0;

which will set to_outstanding_quantity to be the same as to_completed_quantity.
Now I would like to set it back to exactly how it was before (generated ), how do I do that?
I’ve tried this but I’m getting syntax error :

alter table public.case_change ALTER column to_outstanding_quantity  SET  GENERATED ALWAYS(to_quantity - to_cancelled_quantity - to_completed_quantity) STORED NOT NULL;

>Solution :

You are going to have to:

alter table public.case_change drop column to_outstanding_quantity;

alter table public.case_change ADD column to_outstanding_quantity
GENERATED ALWAYS AS (to_quantity - to_cancelled_quantity - to_completed_quantity ) 
STORED;