Re: [HACKERS] ALTER TABLE ADD COLUMN column SERIAL -- unexpected results
Tom Lane wrote: > > Hiroshi Inoue <[EMAIL PROTECTED]> writes: > > Christopher Kings-Lynne wrote: > >> Just out of interest, is there a special reason it's difficult to implement > >> the DEFAULT feature of alter table add column? > > > Without *DEFAULT* we don't have to touch the table file > > at all. With *DEFAULT* we have to fill the new column > > with the *DEFAULT* value for all existent rows. > > Do we? We could simply declare by fiat that the behavior of ALTER ADD > COLUMN is to fill the new column with nulls. Let the user do an UPDATE > to fill the column with a default, if he wants to. I don't like to fill the column of the existent rows but it seems to be the spec. > After all, I'd not > expect that an ALTER that adds a DEFAULT spec to an existing column > would go through and replace existing NULL entries for me. > > This is a little trickier if one wants to make a NOT NULL column, > however. Seems the standard technique for that could be > > ALTER tab ADD COLUMN newcol without the not null spec; > UPDATE tab SET newcol = something; > ALTER tab ALTER COLUMN newcol ADD CONSTRAINT NOT NULL; > Yes I love this also. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ALTER TABLE ADD COLUMN column SERIAL -- unexpected results
Hiroshi Inoue <[EMAIL PROTECTED]> writes: > Christopher Kings-Lynne wrote: >> Just out of interest, is there a special reason it's difficult to implement >> the DEFAULT feature of alter table add column? > Without *DEFAULT* we don't have to touch the table file > at all. With *DEFAULT* we have to fill the new column > with the *DEFAULT* value for all existent rows. Do we? We could simply declare by fiat that the behavior of ALTER ADD COLUMN is to fill the new column with nulls. Let the user do an UPDATE to fill the column with a default, if he wants to. After all, I'd not expect that an ALTER that adds a DEFAULT spec to an existing column would go through and replace existing NULL entries for me. This is a little trickier if one wants to make a NOT NULL column, however. Seems the standard technique for that could be ALTER tab ADD COLUMN newcol without the not null spec; UPDATE tab SET newcol = something; ALTER tab ALTER COLUMN newcol ADD CONSTRAINT NOT NULL; where the last command would verify that the column contains no nulls before setting the flag, just like ALTER TABLE ADD CONSTRAINT does now (but I think we don't have a variant for NULL/NOT NULL constraints). This is slightly ugly, maybe, but it sure beats not having the feature at all. Besides, it seems to me there are cases where you don't really *want* the DEFAULT value to be used to fill the column, but something else (or even want NULLs). Why should the system force an update of every row in the table with a value that might not be what the user wants? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [HACKERS] ALTER TABLE ADD COLUMN column SERIAL -- unexpected results
> *ALTER TABLE* isn't as easy as *CREATE TABLE*. > It has another problem because it hasn't implemented > *DEFAULT* yet. Just out of interest, is there a special reason it's difficult to implement the DEFAULT feature of alter table add column? Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ALTER TABLE ADD COLUMN column SERIAL -- unexpected results
Christopher Kings-Lynne wrote: > > > *ALTER TABLE* isn't as easy as *CREATE TABLE*. > > It has another problem because it hasn't implemented > > *DEFAULT* yet. > > Just out of interest, is there a special reason it's difficult to implement > the DEFAULT feature of alter table add column? > Without *DEFAULT* we don't have to touch the table file at all. With *DEFAULT* we have to fill the new column with the *DEFAULT* value for all existent rows. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] ALTER TABLE ADD COLUMN column SERIAL -- unexpected results
Tom Lane wrote: > > "Rod Taylor" <[EMAIL PROTECTED]> writes: > > Running: > > ALTER TABLE table ADD COLUMN column SERIAL; > > Defines a column as int4 but does not create the sequence or attempt > > to set the default value. > > Yeah ... SERIAL is implemented as a hack in the parsing of CREATE > TABLE, but there's no corresponding hack in ALTER TABLE. A bug, > no doubt about it, but I don't much like the obvious fix of duplicating > the hack in two places. Isn't there a cleaner way to deal with this > "data type"? > *ALTER TABLE* isn't as easy as *CREATE TABLE*. It has another problem because it hasn't implemented *DEFAULT* yet. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ALTER TABLE ADD COLUMN column SERIAL -- unexpected results
> "Rod Taylor" <[EMAIL PROTECTED]> writes: > > Running: > > ALTER TABLE table ADD COLUMN column SERIAL; > > Defines a column as int4 but does not create the sequence or attempt > > to set the default value. > > Yeah ... SERIAL is implemented as a hack in the parsing of CREATE > TABLE, but there's no corresponding hack in ALTER TABLE. A bug, > no doubt about it, but I don't much like the obvious fix of duplicating > the hack in two places. Isn't there a cleaner way to deal with this > "data type"? Added to TODO. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] ALTER TABLE ADD COLUMN column SERIAL -- unexpected results
"Rod Taylor" <[EMAIL PROTECTED]> writes: > Running: > ALTER TABLE table ADD COLUMN column SERIAL; > Defines a column as int4 but does not create the sequence or attempt > to set the default value. Yeah ... SERIAL is implemented as a hack in the parsing of CREATE TABLE, but there's no corresponding hack in ALTER TABLE. A bug, no doubt about it, but I don't much like the obvious fix of duplicating the hack in two places. Isn't there a cleaner way to deal with this "data type"? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] ALTER TABLE ADD COLUMN column SERIAL -- unexpected results
Running: ALTER TABLE table ADD COLUMN column SERIAL; Defines a column as int4 but does not create the sequence or attempt to set the default value. Not a big deal, but I was surprised when the column values were null. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. BEGIN:VCARD VERSION:2.1 N:Taylor;Rod;B FN:Taylor, Rod B ORG:BarChord Entertainment Inc.;Network Operation and Development TITLE:Systems Engineer ADR;WORK:;;;Toronto;Ontario;;Canada LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Toronto, Ontario=0D=0ACanada X-WAB-GENDER:2 URL;WORK:http://www.barchord.com BDAY:19790401 EMAIL;INTERNET:[EMAIL PROTECTED] EMAIL;PREF;INTERNET:[EMAIL PROTECTED] EMAIL;INTERNET:[EMAIL PROTECTED] REV:20010716T164811Z END:VCARD ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
