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
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
