Обсуждение: Implicit sequence with start value?

Hello,

Is it possible to use an implicit sequence with a start value?
Something like:  CREATE TABLE foo (key SERIAL START 1000 PRIMARY KEY NOT NULL);

Thank you in advance, Clemens

Clemens Eisserer <linuxhippy@gmail.com> writes:
> Is it possible to use an implicit sequence with a start value?
> Something like:  CREATE TABLE foo (key SERIAL START 1000 PRIMARY KEY NOT NULL);

Well, you can't do it just that way, but you could issue a setval() or
ALTER SEQUENCE command after creating the table.  For instance

regression=# create table foo (bar serial);
NOTICE:  CREATE TABLE will create implicit sequence "foo_bar_seq" for serial column "foo.bar"
CREATE TABLE
regression=# alter sequence foo_bar_seq start with 1000;
ALTER SEQUENCE

or you might prefer

regression=# select setval(pg_get_serial_sequence('foo', 'bar'), 1000);
 setval
--------
   1000
(1 row)


            regards, tom lane

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

On 26/07/2009 18:20, Clemens Eisserer wrote:
> 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:

That's because type SERIAL sets the column's *default* value to call
nextval() - so if you supply an explicit value, it will use that instead.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On Sun, Jul 26, 2009 at 11:20 AM, Clemens Eisserer<linuxhippy@gmail.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');

The problem is that I don't have control over the SQL generated, that
happens behind the scenes of the JDBCRowSet/SwingSet toolkt/api.
Fourtunatly it seems to work when I simply don't bind the textfield :)

However now I am facing other, jdbc-driver related problems, however
I'll ask on the jdk-list.

Thanks for all your help and patience, Clemens

2009/7/26 Scott Marlowe <scott.marlowe@gmail.com>:
> On Sun, Jul 26, 2009 at 11:20 AM, Clemens Eisserer<linuxhippy@gmail.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');
>