Add a column with a default value to an existing table in postgresql

Question

Question:
Is there a postgres query to add a new column to an existing table and to automatically populate that column for all rows of the table with a certain value, let's say "A1", just once, as the column is created, so that I can still set the DEFAULT value of the column to another value, let's say "B2"?

Just to be clear, I am looking for something like this:

Given my_table:

name   |   work
------------------------
bob    |  fireman
carl   |  teacher
alice  |  policeman

my query

ALTER TABLE my_table 
ADD COLUMN description varchar(100) 
DEFAULT "B2"
COMMAND_I_D_WISH_TO_KNOW "A1";

changes my_table into

name   |   work       | description
-------------------------------------
bob    |  fireman     | "A1"
carl   |  teacher     | "A1"
alice  |  policeman   | "A1"

so that if afterwards I run the query

INSERT INTO my_table(name, work)
VALUES karen, developer;

my_tables becomes

name   |   work       | description
-------------------------------------
bob    |  fireman     | "A1"
carl   |  teacher     | "A1"
alice  |  policeman   | "A1"
karen  |  developer   | "B2"

Answer 1

Referencing the most recent docs, this operation can be done using two statements.

1. Adds the column with the old default value

ALTER TABLE my_table ADD COLUMN description varchar(100) DEFAULT 'A1';

2. Modifies the column to use a different default value

ALTER TABLE my_table ALTER COLUMN description SET DEFAULT 'B2'

A full reproducible sample has been included below:

CREATE TABLE my_table (
  "name" VARCHAR(5),
  "work" VARCHAR(9)
);

INSERT INTO my_table
  ("name", "work")
VALUES
  ('bob', 'fireman'),
  ('carl', 'teacher'),
  ('alice', 'policeman');

---

Query #1

select * from my_table;

name	work
bob	fireman
carl	teacher
alice	policeman

---

Query #2

ALTER TABLE my_table 
ADD COLUMN description varchar(100) 
DEFAULT 'A1';

There are no results to be displayed.

---

Query #3

select * from my_table;

name	work	description
bob	fireman	A1
carl	teacher	A1
alice	policeman	A1

---

Query #4

ALTER TABLE my_table 
ALTER COLUMN description SET DEFAULT 'B2';

There are no results to be displayed.

---

Query #5

INSERT INTO my_table("name", "work")
VALUES ('karen', 'developer');

There are no results to be displayed.

---

Query #6

select * from my_table;

name	work	description
bob	fireman	A1
carl	teacher	A1
alice	policeman	A1
karen	developer	B2

---

View working demo on DB Fiddle

Let me know if this works for you.

Answer 2

Yes, you can do that by using two actions in one ALTER.

ALTER TABLE my_table 
  ADD COLUMN description varchar(100) DEFAULT 'A1', 
  ALTER COLUMN description SET DEFAULT 'B2';

I have verified that DROP DEFAULT doesn't work in the same command as the column was added in, unlike SET DEFAULT NULL. I don't think there is really a reason for it not to work, it just happens to do them in the wrong order for it to work, and no one has bothered to force it to do it in the right order. (Perhaps because no one even tested that exact thing and so realizes it is broken)

You can always just do them in different ALTER commands. If you are worried that someone might "see" the table in an in-between state, you can do both in a single transaction. Then the lock is not released in between so no one can see it. The real advantage of doing multiple actions in one command is that the table doesn't need to be rewritten multiple times. But here, it doesn't need to get rewritten at all anyway.