Thread: Inserting Default Values

    Hi all.  I just started using postgres for a website I'm writing,
and so I was messing around with some of it's features and I'm running
into a little problem.
    Pretty much, it has to do with default values on the first column
of a row.

    Say I have a table that looks like the following:

[Table name 'test']
 name      | character varying(16) |
 id        | integer               | not null default
nextval('"test_id_seq"'::text)


    If I run:

sql=> insert into test values('blah');

    It inserts just fine, automatically taking the default value for
id.  However, the table I'm actually using is the reverse of the previous.
Example:

[Table name 'status']
 id        | integer               | not null default
nextval('"status_id_seq"'::text)
 name      | character varying(32) |


    The problem here is that no matter what I do, I can't get it to
just take the default value for id.  I've tried leaving a blank, followed
by a comma as in:

sql=> insert into test values (,'blah');

    But that errors out.  I've messed with using the nextval()
function, but that seems to run before the actual query is run, so if
there is an error in the query, my sequence goes to the next value
anyways.  This won't work for me because if the query fails, I don't want
to have gaps in id #'s.

    Anyways, does anyone have any idea how to get around this problem?
Any help you could give me would be great.  Thanks.

     ________________________________________________________________________
                Mike Barrett | "I used to read, now I go to raves."
             mike@daboyz.org | -- Random MUNI Rider, speaking
              www.daboyz.org |    to my friend Allison.
     ------------------------+-----------------------------------------------

On Thu, 7 Feb 2002, Mike Barrett wrote:
>     It inserts just fine, automatically taking the default value for
> id.  However, the table I'm actually using is the reverse of the previous.
> Example:
>
> [Table name 'status']
>  id        | integer               | not null default
> nextval('"status_id_seq"'::text)
>  name      | character varying(32) |
>
> sql=> insert into test values (,'blah');

insert into test (name) values ('blah');

--
Tycho Fruru            tycho.fruru@conostix.com

Mike Barrett <mike@daboyz.org> writes:

>     The problem here is that no matter what I do, I can't get it to
> just take the default value for id.  I've tried leaving a blank, followed
> by a comma as in:
>
> sql=> insert into test values (,'blah');

This was just discussed a week or two ago.  The proper syntax is:

INSERT INTO test (name) VALUES ('blah');

>     But that errors out.  I've messed with using the nextval()
> function, but that seems to run before the actual query is run, so if
> there is an error in the query, my sequence goes to the next value
> anyways.  This won't work for me because if the query fails, I don't want
> to have gaps in id #'s.

You *will* have gaps in IDs if you use sequences and have rolled-back
transactions.  See the docs and the FAQ.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863