Thread: 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'); >