Difference between "add column" and "add column" with default

On Mon, Mar 20, 2006 at 10:52:36AM +0100, Guido Neitzer wrote:

Hi.

What is the technical difference between adding a column to a table
and then apply a "set value = ..." to all columns and adding a column
with a default value = ...?

What version are you using:

# alter table a add column b int4 default 0;
ERROR: adding columns with defaults is not implemented

The latter doesn't work in a single step. The former does indeed
duplicate all the rows.

I have seen that the first duplicates all rows, I had to vacuum and
reindex the whole table. Okay so far, I have expected this. But this
wasn't necessary with the second option, nevertheless, fetching some
rows showed, that the value of the new column was my default value.

The latter only affects newly inserted rows, changing the default does
not affect any existing rows. If it does, please provide examples.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On 20.03.2006, at 11:41 Uhr, Martijn van Oosterhout wrote:

What is the technical difference between adding a column to a table
and then apply a "set value = ..." to all columns and adding a column
with a default value = ...?

What version are you using:

# alter table a add column b int4 default 0;
ERROR: adding columns with defaults is not implemented

DB=# show server_version;
server_version
----------------
8.1.3
(1 row)

# alter table a add column b int4 default 0;

works just fine.

The latter doesn't work in a single step. The former does indeed
duplicate all the rows.

It works here.

The latter only affects newly inserted rows, changing the default does
not affect any existing rows. If it does, please provide examples.

Nope it doesn't. If I add the column with a default constraint, all
rows have the default value.

Example:

DB=# create table test (id int4, a int4);
CREATE TABLE
DB=# insert into test values (1, 1);
INSERT 0 1
DB=# insert into test values (2, 2);
INSERT 0 1
DB=# insert into test values (3, 3);
INSERT 0 1
DB=# select * from test;
id | a
----+---
1 | 1
2 | 2
3 | 3
(3 rows)

DB=# alter table test add column b int4 default 0;
ALTER TABLE
DB=# select * from test;
id | a | b
----+---+---
1 | 1 | 0
2 | 2 | 0
3 | 3 | 0
(3 rows)

DB=# alter table test add column c int4 default 17;
ALTER TABLE
DB=# select * from test;
id | a | b | c
----+---+---+----
1 | 1 | 0 | 17
2 | 2 | 0 | 17
3 | 3 | 0 | 17
(3 rows)

cug

--
PharmaLine, Essen, GERMANY
Software and Database Development

Guido Neitzer <guido.neitzer@pharmaline.de> writes:

What is the technical difference between adding a column to a table
and then apply a "set value = ..." to all columns and adding a column
with a default value = ...?

"ADD COLUMN DEFAULT ..." is implemented via a full-table rewrite,
so you end up with a version of the table that has no dead space.
Unfortunately this requires an exclusive table lock while the rewrite
happens, so you lock out other processes from the table for a
considerably longer period of time than the UPDATE approach. IIRC it's
also not completely MVCC-safe --- committed-dead rows will get removed
even if there are old open transactions that should still see those rows
as current. Bottom line: there's no free lunch.

regards, tom lane