Question 114528 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Add default value only to new rows

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search