Re: automatic insert of next sequence value?

* J B Bell <jbbell@intergate.ca> [001012 10:13] wrote:
> Hi,
>
> I've recently started with pgsql after having used mysql.  Oddly, a problem
> the latter seems to have followed me.
>
> 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.
>
> Is there some settable option that determines what will cause a default value
> to be inserted?  Is this a compile-time option of some sort?  I've been
> searching around for a couple of weeks on this, with no luck in the docs.  I
> would be very grateful for any assistance.

This is because you're abusing INSERT, the porper way to do insert is
to specifify which columns explicitly:

INSERT INTO foo (col1, col3) VALUES ( 5, 6 );

now, if col2 has a default value it will be set properly.

-Alfred