Adding column with defaults without retrofitting value

Question

If I have a table like this:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    address VARCHAR(255)
);
INSERT INTO customers(id,address)
VALUES (1,'Here'),(2,NULL);

I know I can add an additional column with a default as follows:

ALTER TABLE customers
ADD more VARCHAR(255) DEFAULT 'etc';

In PostgreSQL, MariaDB, SQLite and Oracle, when I do this the default is retrofitted to all columns. However I don’t want that to happen, since not all of the customers actually have an address.

In Microsoft SQL, the default is only retrofitted if the clause WITH VALUES is added.

How can I get the others not to retrofit the default?

Answer 1

For PostgreSQL you can do it in two steps, first add the new column and second set the new default for the column:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    address VARCHAR(255)
);
INSERT INTO customers(id,address)
VALUES (1,'Here'),(2,NULL);

-- step one:
ALTER TABLE customers
ADD more VARCHAR(255) ;
-- step two:
ALTER TABLE customers ALTER COLUMN more SET DEFAULT 'etc';
-- test:
SELECT * FROM customers;