Re: [SQL] insert default into serial fields?

"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