pgsql/doc TODO

Log message:
Fix from Neil Conway:

< o ALTER TABLE ADD COLUMN column SET DEFAULT should
fill existing

o ALTER TABLE ALTER COLUMN column SET DEFAULT

should fill existing

Wha! Altering a column default surely shouldn't change existing nulls or
something???? All you're saying is from now on, the default should be blah.
Surely it was correct in the first place?

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

Log message:
Fix from Neil Conway:

< o ALTER TABLE ADD COLUMN column SET DEFAULT should
fill existing

o ALTER TABLE ALTER COLUMN column SET DEFAULT

should fill existing

Wha! Altering a column default surely shouldn't change existing nulls or
something???? All you're saying is from now on, the default should be blah.
Surely it was correct in the first place?

I agree with Chris --- ALTER COLUMN SET DEFAULT should *not* change any
existing data. (SQL92 thinks so too.) The real error in the original
TODO item was the reference to "SET DEFAULT", which is not the syntax
used in ADD COLUMN. I think the TODO item should read more like

"ALTER TABLE ADD COLUMN ... DEFAULT foo" should work

Right now we get

regression=# alter table foo add column bar int default 42;
ERROR: Adding columns with defaults is not implemented.
Add the column, then use ALTER TABLE SET DEFAULT.

The reason this is rejected is that per spec it ought to fill all rows
with 42, and we don't have code in place to do that. But our ALTER
COLUMN SET DEFAULT is not broken; it works per spec.

regards, tom lane

Tom Lane wrote:

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

Log message:
Fix from Neil Conway:

< o ALTER TABLE ADD COLUMN column SET DEFAULT should
fill existing

o ALTER TABLE ALTER COLUMN column SET DEFAULT

should fill existing

Wha! Altering a column default surely shouldn't change existing nulls or
something???? All you're saying is from now on, the default should be blah.
Surely it was correct in the first place?

I agree with Chris --- ALTER COLUMN SET DEFAULT should *not* change any
existing data. (SQL92 thinks so too.) The real error in the original
TODO item was the reference to "SET DEFAULT", which is not the syntax
used in ADD COLUMN. I think the TODO item should read more like

"ALTER TABLE ADD COLUMN ... DEFAULT foo" should work

Right now we get

regression=# alter table foo add column bar int default 42;
ERROR: Adding columns with defaults is not implemented.
Add the column, then use ALTER TABLE SET DEFAULT.

The reason this is rejected is that per spec it ought to fill all rows
with 42, and we don't have code in place to do that. But our ALTER
COLUMN SET DEFAULT is not broken; it works per spec.

OK, TODO updated to:

o ALTER TABLE ADD COLUMN column SET DEFAULT should fill
existing rows with DEFAULT value

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

OK, TODO updated to:
o ALTER TABLE ADD COLUMN column SET DEFAULT should fill
existing rows with DEFAULT value

Syntax is still wrong --- there is no SET in ADD COLUMN.

regards, tom lane

OK, now is:

o ALTER TABLE ADD COLUMN column DEFAULT should fill existing
rows with DEFAULT value

---------------------------------------------------------------------------

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

OK, TODO updated to:
o ALTER TABLE ADD COLUMN column SET DEFAULT should fill
existing rows with DEFAULT value

Syntax is still wrong --- there is no SET in ADD COLUMN.

regards, tom lane

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026