Implicit sequence with start value?

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

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>: