Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA95892 for ; Tue, 7 Mar 2000 03:37:01 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id DAA19503; Tue, 7 Mar 2000 03:36:33 -0500 (EST) To: "Seth Banks" cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] insert default into serial fields? In-reply-to: References: Comments: In-reply-to "Seth Banks" message dated "Mon, 06 Mar 2000 23:22:55 -0800" Date: Tue, 07 Mar 2000 03:36:33 -0500 Message-ID: <19500.952418193@sss.pgh.pa.us> From: Tom Lane "Seth Banks" writes: > what's the appropriate way to go about inserting values into a table with a > serial field? > i have tried '', 'default', and default...but none seem to work correctly. > any help? INSERT ... VALUES(..., DEFAULT, ...) ought to work according to SQL92, but we don't support it currently. I recommend using an explicit column list in INSERT: INSERT INTO table(col1,col2,col4) VALUES(val1,val2,val4) where you omit the serial column's name (col3, maybe, in this example). Another way is to explicitly give the same expression as the column's default value: nextval('sequence-object-name'). But that's pretty ugly because it ties your code to the current implementation of SERIAL. One great advantage of explicitly listing the column names is that your INSERT code doesn't break if you add or reorder columns in the table declaration. IMHO that makes up for the tedium of writing them all out, but you might see it differently... regards, tom lane