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 NAA00825 for ; Sun, 27 Feb 2000 13:29:28 -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 NAA09910; Sun, 27 Feb 2000 13:29:21 -0500 (EST) To: Peter Eisentraut cc: Emils Klotins , pgsql-sql@postgreSQL.org Subject: Re: [SQL] INSERT w/o variable names for a SERIAL type? In-reply-to: References: Comments: In-reply-to Peter Eisentraut message dated "Sun, 27 Feb 2000 16:18:39 +0100" Date: Sun, 27 Feb 2000 13:29:21 -0500 Message-ID: <9907.951676161@sss.pgh.pa.us> From: Tom Lane Peter Eisentraut writes: > Emils Klotins writes: >> Now, if I want to write a general #define, I'd need to have a way >> to specify 'default' for SERIAL field, for, if I omit the id field in >> VALUES, I need to specify all the rest of the fields explicitly. >> >> Is there any value I could put in place of id in VALUES part, to >> make it replaced with the next value in sequence? > INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...); I think that is legal SQL92 syntax, but Postgres doesn't accept it at present. The usual recommendation is to call out the columns you are loading explicitly: INSERT INTO my_table(a,b,d) VALUES (val-for-a, val-for-b, val-for-d); The ones you don't load get their default values substituted instead. This way is a shade more verbose, but it's good solid defensive programming practice: the insert will do what it's supposed to even if the table schema changes to add/delete/reorder columns. regards, tom lane