Add default value only to new rows-CodePudding

I want to add a new column that is NULL for existing rows, but has default value for new rows.

This:

ALTER TABLE foo ADD COLUMN bar timestamp NULL DEFAULT clock_timestamp();

Won't work, because it would add default value to existing rows.

This would work:

ALTER TABLE foo ADD COLUMN bar timestamp NULL;
ALTER TABLE foo ALTER bar SET DEFAULT clock_timestamp();

but I need to run 2 queries. Is it possible to do in 1 query?

CodePudding user response：

No, you need to run two statements. That shouldn't be a problem. If you are worried about race conditions, run the statements in a single transaction.

CodePudding user response：

Per documentation Alter Table:

To add a column and fill it with a value different from the default to be used later:

ALTER TABLE transactions ADD COLUMN status varchar(30) DEFAULT 'old', ALTER COLUMN status SET default 'current';

Existing rows will be filled with old, but then the default for subsequent commands will be current. The effects are the same as if the two sub-commands had been issued in separate ALTER TABLE commands.

So to do it in one statement:

\d animals
                        Table "public.animals"
   Column   |          Type          | Collation | Nullable | Default 
------------ ------------------------ ----------- ---------- ---------
 pk_animals | integer                |           | not null | 
 cond       | character varying(200) |           | not null | 
 animal     | character varying(200) |           | not null | 

alter table animals add column ts timestamp null, alter column ts set default now();

 select * from animals;
 pk_animals | cond  | animal | ts 
------------ ------- -------- ----
         16 | fair  | heron  | 
        101 | great | gopher | 
          1 | good  | crow   | 
          2 | good  | eagle  | 
          3 | good  | mole   | 
         33 | fair  | mole   | 
         35 | fair  | emu    | 
         24 | great | rabbit | 
         22 | poor  | not    | 
        200 | fair  | crow   | 
         32 | good  | not    | 
         37 | great | not    | 

insert into animals values (23, 'good', 'lion');

 select * from animals;
 pk_animals | cond  | animal |             ts             
------------ ------- -------- ----------------------------
         16 | fair  | heron  | 
        101 | great | gopher | 
          1 | good  | crow   | 
          2 | good  | eagle  | 
          3 | good  | mole   | 
         33 | fair  | mole   | 
         35 | fair  | emu    | 
         24 | great | rabbit | 
         22 | poor  | not    | 
        200 | fair  | crow   | 
         32 | good  | not    | 
         37 | great | not    | 
         23 | good  | lion    | 2022-09-21 14:54:22.987311