I tried doing numerous searches to see if this has been discussed before,
but I couldn't find anything. If it has, perhaps my question can be
answered simply by a pointer to a previous thread.
I'm trying to convert a program from mysql to postgres (yes, I've seen the
light). I'm running into some problems though. From everything I've
read, the SERIAL type is supposed to be like using an auto_increment in
mysql. However, when I try to mix and match user assigned ids (my serial
column) with database generated ids things break.
I've created a simple example (a.sql) which shows the problem.
--------------------
drop table A;
create table A (
id SERIAL PRIMARY KEY,
foo int default 5,
bar int default 10
);
insert into A (id, foo, bar) values (1, 1, 1);
insert into A (id, foo, bar) values (2, 2, 2);
insert into A (id, foo, bar) values (3, 3, 3);
insert into A (id, foo, bar) values (4, 4, 4);
insert into A (foo, bar) values (5, 5);
insert into A (foo, bar) values (6, 6);
--------------------------
The output that I get is:
[EMAIL PROTECTED] Setup]$ p < a.sql
DROP TABLE
NOTICE: CREATE TABLE will create implicit sequence 'a_id_seq' for SERIAL
column 'a.id'
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'a_pkey'
for table 'a'
CREATE TABLE
INSERT 44289 1
INSERT 44290 1
INSERT 44291 1
INSERT 44292 1
ERROR: Cannot insert a duplicate key into unique index a_pkey
ERROR: Cannot insert a duplicate key into unique index a_pkey
--------------------------
However, if I add some debugging statements to see what I'm doing wrong,
the problem seems to go away (sort of).
----------------------
drop table A;
create table A (
id SERIAL PRIMARY KEY,
foo int default 5,
bar int default 10
);
insert into A (id, foo, bar) values (1, 1, 1);
Select * from A;
Select nextval('a_id_seq');
insert into A (id, foo, bar) values (2, 2, 2);
Select * from A;
Select nextval('a_id_seq');
insert into A (id, foo, bar) values (3, 3, 3);
Select * from A;
Select nextval('a_id_seq');
insert into A (id, foo, bar) values (4, 4, 4);
Select * from A;
Select nextval('a_id_seq');
insert into A (foo, bar) values (5, 5);
Select * from A;
Select nextval('a_id_seq');
insert into A (foo, bar) values (6, 6);
Select * from A;
Select nextval('a_id_seq');
-----------------------------
Then the output is:
[EMAIL PROTECTED] Setup]$ p < a.sql
DROP TABLE
NOTICE: CREATE TABLE will create implicit sequence 'a_id_seq' for SERIAL
column 'a.id'
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'a_pkey'
for table 'a'
CREATE TABLE
INSERT 44319 1
id | foo | bar
----+-----+-----
1 | 1 | 1
(1 row)
nextval
---------
1
INSERT 44320 1
id | foo | bar
----+-----+-----
1 | 1 | 1
2 | 2 | 2
(2 rows)
nextval
---------
2
(1 row)
INSERT 44321 1
id | foo | bar
----+-----+-----
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
(3 rows)
nextval
---------
3
(1 row)
INSERT 44322 1
id | foo | bar
----+-----+-----
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
4 | 4 | 4
(4 rows)
nextval
---------
4
(1 row)
INSERT 44323 1
id | foo | bar
----+-----+-----
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
4 | 4 | 4
5 | 5 | 5
(5 rows)
nextval
---------
6
(1 row)
INSERT 44324 1
id | foo | bar
----+-----+-----
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
4 | 4 | 4
5 | 5 | 5
7 | 6 | 6
(6 rows)
nextval
---------
8
(1 row)
---------------------------------------
So, no more errors from the database, but it seems to skip the "6" id
completely. Can anybody explain what I'm seeing? This is with postgresql
7.3.3.
Thanks very much in advance!
Chad
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
