It seems worth pointing out, too, that some SQL purists propose not relying on product-specific methods of auto-incrementing.
I.e., it is possible to do something like: insert into foo( col, ... ) values( coalesce( ( select max( col ) from foo ), 0 ) + 1, ... ); and this is easily placed in a trigger. -tfo In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] (Tom Lane) wrote: > Justin Clift <[EMAIL PROTECTED]> writes: > > Oliver Elphick wrote: > >> I created a sequence using SERIAL when I created a table. I used the > >> same sequence for another table by setting a column default to > >> nextval(sequence). > >> > >> I deleted the first table. The sequence was deleted too, leaving the > >> default of the second table referring to a non-existent sequence. > > > This sounds like a serious bug in our behaviour, and not something we'd > > like to release. > > We will be releasing it whether we like it or not, because > nextval('foo') doesn't expose any visible dependency on sequence foo. > > (If you think it should, how about nextval('fo' || 'o')? If you think > that's improbable, consider nextval('table' || '_' || 'col' || '_seq').) > > The long-term answer is to do what Rod alluded to: support the > Oracle-style syntax foo.nextval, so that the sequence reference is > honestly part of the parsetree and not buried inside a string > expression. > > In the meantime, I consider that Oliver was misusing the SERIAL > feature. If you want multiple tables fed by the same sequence object, > you should create the sequence as a separate object and then create > the tables using explicit "DEFAULT nextval('foo')" clauses. Doing what > he did amounts to sticking his fingers under the hood of the SERIAL > implementation; if he gets his fingers burnt, it's his problem. > > > Specifically in relation to people's existing scripts, and also to > > people who are doing dump/restore of specific tables (it'll kill the > > sequences that other tables depend on too!) > > 7.3 breaks no existing schemas, because older schemas will be dumped > as separate CREATE SEQUENCE and CREATE TABLE ... DEFAULT nextval() > commands. > > 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
