Skip site navigation (1) Skip section navigation (2)

Re: [SQL] insert default into serial fields?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Seth Banks" <seth(at)subimage(dot)com>
Cc: pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [SQL] insert default into serial fields?
Date: 2000-03-07 08:36:33
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
"Seth Banks" <seth(at)subimage(dot)com> 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

In response to

pgsql-sql by date

Next:From: zoltan.sebestyenDate: 2000-03-07 12:25:06
Subject: RE: [SQL] index file's growing big
Previous:From: Robert ChalmersDate: 2000-03-07 07:47:13
Subject: MS Access suddenly refusing to deal with pgsql db... and Failing Make

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group