By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,710 Members | 1,887 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,710 IT Pros & Developers. It's quick & easy.

sequence behavior - is this correct

P: n/a
Hello,
I would like to ask if my problem with sequence is a proper behavior or
this is a bug (probably not)...

I have a table:

CREATE TABLE "testtable" (
"serialfield" SERIAL,
"someotherfield" TEXT,
PRIMARY KEY("serialfield")
) WITH OIDS;

After creation of this table, sequence "testtable_serialfield_seq" is
automatically created, with start value set to "1".

CREATE SEQUENCE "public"."testtable_serialfield_seq"
INCREMENT 1 MINVALUE 1
MAXVALUE 9223372036854775807 START 1
CACHE 1;

And now I have to insert into this table some records from other rdbms
(like MySQL) using sql inserts generated by e.g. mysqldump - it is a
simple import of data, where "serialfield" has got some value...

INSERT INTO testtable VALUES (1, 'sdsdsd');
INSERT INTO testtable VALUES (5, 'sdsdsd');
INSERT INTO testtable VALUES (10, 'sdsdsd');

And my question is: how sequence should react for this ? Right now start
value of the sequence is still "1", so if I will make a query like
"INSERT INTO testtable (someotherfield) VALUES ('sdsdsd')" then new
record's serialfield will have value set to "1" - of course this record
will not be created, because of primary key - but next insert will
create a new record with serialfield set to "2".

I didn't notice this problem before, because I have never made any
imports of data in this way..

Window/Cygwin
PostgreSQL 8.0.0beta3
Regards,
ML


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
That is correct behavior. The sequence value only updates when you use
the sequence value. If you put your own data into the sequence field,
as you would be doing in a conversion or import, the sequence does not
change.

To manually change the sequence values, refer to the script I posted
on Oct. 11, titled "[GENERAL] update sequence conversion script",
which will go through all your tables with sequences and
update them. (you can probably find the post in the archives,
http://archives.postgresql.org)

Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

__________________________________________________ ______________________________

Hello,
I would like to ask if my problem with sequence is a proper behavior or
this is a bug (probably not)...

I have a table:

CREATE TABLE "testtable" (
"serialfield" SERIAL,
"someotherfield" TEXT,
PRIMARY KEY("serialfield")
) WITH OIDS;

After creation of this table, sequence "testtable_serialfield_seq" is
automatically created, with start value set to "1".

CREATE SEQUENCE "public"."testtable_serialfield_seq"
INCREMENT 1 MINVALUE 1
MAXVALUE 9223372036854775807 START 1
CACHE 1;

And now I have to insert into this table some records from other rdbms
(like MySQL) using sql inserts generated by e.g. mysqldump - it is a
simple import of data, where "serialfield" has got some value...

INSERT INTO testtable VALUES (1, 'sdsdsd');
INSERT INTO testtable VALUES (5, 'sdsdsd');
INSERT INTO testtable VALUES (10, 'sdsdsd');

And my question is: how sequence should react for this ? Right now start
value of the sequence is still "1", so if I will make a query like
"INSERT INTO testtable (someotherfield) VALUES ('sdsdsd')" then new
record's serialfield will have value set to "1" - of course this record
will not be created, because of primary key - but next insert will
create a new record with serialfield set to "2".

I didn't notice this problem before, because I have never made any
imports of data in this way..

Window/Cygwin
PostgreSQL 8.0.0beta3
Regards,
ML


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.