Re: Implicit sequence with start value?

On Sun, Jul 26, 2009 at 11:20 AM, Clemens Eisserer<linuxhippy(at)gmail(dot)com> wrote:
> Hi Tom,
>
>> regression=# alter sequence foo_bar_seq start with 1000;
>> ALTER SEQUENCE
> Completly forgot about that possibility, thanks a lot :)
>
> What still puzzles me is how to get the sequence value applied.
> MySQL's auto_increment simply ignores whatever value is supplied to
> it, however postgres seems to insert the value instead of the next
> sequence value, if one is supplied:
>
>> CREATE TABLE custtype (key SERIAL PRIMARY KEY NOT NULL, name VARCHAR(127) NOT NULL,  short VARCHAR(4));
>> ALTER SEQUENCE custtype_key_seq START WITH 10000;");
>> INSERT INTO custtype VALUES(0, 'test', 'ta');
>>
>>  key |      name      | short
>> -----+----------------+-------
>  >  0   | test      | ta
>
> Of course, under normal circumstances it would be no problem to insert
> a nextval() however I am using an updateable JDBC ResultSet.
> Any idea how I can force the sequence's nextval() value into the key
> column using ResultSets?
>
> Thank you in advance, Clemens

Two methods:

1: Don't include the column in the insert:

INSERT INTO custtype ("name",short) VALUES('test', 'ta');

2: Use the DEFAULT keyword:

INSERT INTO custtype (key, "name",short) VALUES(DEFAULT, 'test', 'ta');