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"

CodePudding user response：

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;

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;

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;

View working demo on DB Fiddle

Let me know if this works for you.

CodePudding user response：

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';