Click here to Skip to main content
15,446,510 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all!

I have the following problem. I've created a table in Postgres.

SQL
CREATE SEQUENCE test_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE test_id_seq OWNER TO admin;

CREATE TABLE test
(
  id bigserial NOT NULL,
  data character varying(128) NOT NULL,
  CONSTRAINT pkey_id_test PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE test OWNER TO admin;

CREATE OR REPLACE RULE replace_test AS
    ON INSERT TO test
   WHERE (EXISTS ( SELECT 1
           FROM test
          WHERE test.id = new.id)) DO INSTEAD  UPDATE test SET data = new.data
  WHERE test.id = new.id;

ALTER TABLE test
  ADD CONSTRAINT pkey_id_test PRIMARY KEY(id);

ALTER TABLE test ADD COLUMN id bigint;
ALTER TABLE test ALTER COLUMN id SET STORAGE PLAIN;
ALTER TABLE test ALTER COLUMN id SET NOT NULL;
ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq'::regclass);
ALTER TABLE test ADD COLUMN data character varying(128);
ALTER TABLE test ALTER COLUMN data SET STORAGE EXTENDED;
ALTER TABLE test ALTER COLUMN data SET NOT NULL;


Performing the following query:

SQL
INSERT INTO "test" ("data") VALUES ('1')


I've inserted some test data into the table.

Now:

SELECT "id" FROM "test" ORDER BY "id"


It gives me:

2
5
10
17
26
37
50


Ids aren't incremented by 1. Where is the problem? Did I miss something?
Thanks!
Posted
Updated 23-Aug-10 8:30am
v3

Looking at the values (2, 5, 10, etc), it seems that you have something that is running the 'increment' process 2n times, where n is the number of records already in the test table. The successive differences between your id's are 3, 5, 7, 9, 11, 13, which looks to me very much like a 2*n + 1 pattern. Don't ask me how or why; I've just spotted "what". I hope this gives you or someone else a clue as to where to start digging. Good luck!
Peter
 
Share this answer
 
Comments
Nyarost 24-Aug-10 1:39am    
Thaks, cap XD.
Sorry for bothering, guys. Just bringing up the question. I really need help. We are moving from MySQL to PostgreSQL
 
Share this answer
 
Comments
Peter_in_2780 24-Aug-10 23:19pm    
If you're happy, it's polite to vote for good answers, and mark one (or more) as accepted so the "case is closed".
Nyarost 25-Aug-10 3:16am    
Hmmm? Did I mentioned that it is resolved? It's not :( I have only temporary solution using another sequence for id generation.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900