Thread: Difference between "add column" and "add column" with default
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 = ...? 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. So, I'm curious: what happens (not) here? Thx for the explanation. cug
Attachment
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/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
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
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