Received: from wallace.ece.rice.edu (root@wallace.ece.rice.edu [128.42.12.154]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA51922 for ; Wed, 8 Sep 1999 11:23:03 -0400 (EDT) (envelope-from reedstrm@wallace.ece.rice.edu) Received: by wallace.ece.rice.edu via sendmail from stdin id (Debian Smail3.2.0.102) for pgsql-sql@postgreSQL.org; Wed, 8 Sep 1999 10:22:58 -0500 (CDT) Date: Wed, 8 Sep 1999 10:22:58 -0500 From: "Ross J. Reedstrom" To: Hroi Sigurdsson Cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] DEFAULT confusion Message-ID: <19990908102258.B24655@wallace.ece.rice.edu> References: <37D5CC72.9A7CBD49@ninja.dk> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 0.95.3i In-Reply-To: <37D5CC72.9A7CBD49@ninja.dk>; from Hroi Sigurdsson on Wed, Sep 08, 1999 at 02:39:46AM +0000 On Wed, Sep 08, 1999 at 02:39:46AM +0000, Hroi Sigurdsson wrote: > Hello postgresql'ers (how do you pronounce that?). > > Suppose i have the following sequence, table and index: > > CREATE SEQUENCE stuff_seq; > CREATE TABLE stuff ( > id INTEGER DEFAULT NEXTVAL('stuff_seq') NOT NULL, > name TEXT, > number INTEGER > ); > CREATE UNIQUE INDEX stuff_id ON tabel(id); > > Then to properly insert rows i have to > > INSERT INTO tabel VALUES (NEXTVAL('tabel_seq'), "sometext", 123); > > I can't just > INSERT INTO tabel VALUES (NULL, "something", 123); Multiple errors here, BTW: your table name is "stuff" not "tabel", and "something" is a field or table name, 'something' is a quoted string. > > Then what is the point of the DEFAULT clause? In other words: How do I > get away with not specifying anything for id? And how (if Herouth and Thomas Mack have already pointed out the correct INSERT syntax, (good rant, Herouth!) so I won't deal with this here. INSERT INTO stuff (name,number) VALUES ('something',123); > possible/recommendable) do I force the id value to be nothing but > NEXTVAL('stuff_seq'), ie. not just an arbitrary number? Here you enter the realm of triggers. To use them, you have to write a procedure that can be used as a 'BEFORE INSERT' trigger. However, I think you'll find properly formated INSERT statements will do most of what you want. I just don't insert into my serial columns, ever. Ross -- Ross J. Reedstrom, Ph.D., NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005