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"

Tms91 · Accepted Answer · 2022-05-05 12:28:26Z

10

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.

edited May 5, 2022 at 12:28

Tms91

4,40910 gold badges56 silver badges100 bronze badges

answered Oct 26, 2021 at 23:19

jjanes

45k5 gold badges39 silver badges48 bronze badges

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

4 Comments

jjanes Over a year ago

Works for me, in multiple versions. What version are you using? Is their anything weird about the table (foreign, temp, partitioned, etc.)

jjanes Over a year ago

You can see it working at db-fiddle.com/f/2CFNtdkLbc324etcsSrNTq/0

Tms91 Over a year ago

my bad, it works, I'll remove my comments

Tms91 Over a year ago

I just don't understand why it does not work when I substitute line ALTER COLUMN description SET DEFAULT 'B2'; with line ALTER COLUMN description DROP DEFAULT; . To remove the default, only ALTER COLUMN description SET DEFAULT NULL seems to work.

ggordon · Accepted Answer · 2021-10-26 15:58:10Z

5

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

Adds the column with the old default value

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

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.

edited Oct 26, 2021 at 15:58

user330315

answered Oct 26, 2021 at 15:44

ggordon

10.1k2 gold badges19 silver badges29 bronze badges

2 Comments

Tms91 Over a year ago

Thanks, I was asking if there were a way to run "update table by adding a new column and set its default to A1" and "update table by setting the new column default to B2" commands at once, with just one query.

ggordon Over a year ago

@Tms91 You asked a good question. However, I'm not seeing such in the latest docs (at the time of this post). Maybe they will consider this for future versions, you could submit a request here or engage them further.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related