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

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