Re: automatic insert of next sequence value?

J B Bell <jbbell(at)intergate(dot)ca> writes:

> When I set up a field with a default value to use a sequence (with "nextval"),
> It doesn't work automatically. Both using DBI and the pgsql cli, if I do
>
> INSERT INTO foo VALUES (NULL);
>
> or
>
> INSERT INTO foo VALUES ('');
>
> I get an error or null is inserted in the first case, if the field is set
> non-null, or a 0 in the second case.
>
> Of course without this, a good part of the convenince of auto-incrementing is
> defeated. I can work around it by looking up the appropriate value, but then
> I need to do locking as well, which is likely slower and definitely more
> annoying.

Doing:

INSERT INTO foo(col1, col2, col4) VALUES(val1, val2, val4);

will do what you want, given that col3 is the auto-incrementing
column. I.e. tell PostgreSQL to only set the other columns.

Tomas