INSERT fails because primary key already exists

Question

I'm using PostgreSQL 9.4 and pgAdminIII 1.20 client. When launching an INSERT on a particular table, I get an error message saying: Details: the key (gid)=(31509) already exists. (SQL State: 23505).

I do not enter a gid value in the command in order to let the sequence do the job:

INSERT INTO geo_section (idnum, insee, ident) VALUES (25, '015233', '') ;

The sequence is defined as this:

CREATE SEQUENCE geo_section_gid_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 31509
  CACHE 1;
ALTER TABLE geo_section_gid_seq
  OWNER TO postgres;

The following query returns 34502:

SELECT max(gid) FROM geo_section ;

Therefore, I've tried to alter the sequence in order to start sequence from 34503:

ALTER SEQUENCE geo_section_gid_seq START 34503 ;

I get a success message saying that the query has been executed properly. But the sequence START parameter remains with 31509 value...

Maybe you forgot (after creating the sequence) to attach it to geo_section.gid ? `ALTER TABLE ONLY geo_section ALTER COLUMN gid SET DEFAULT nextval('geo_section_gid_seq'::regclass); ALTER SEQUENCE geo_section_gid_seq OWNED BY geo_section.gid;` — joop, Jun 27 '16 at 11:37

score 7 · Accepted Answer · answered Jun 27 '16 at 09:26

7

To change the next value for sequence use setval function :

     select setval('geo_section_gid_seq'::regclass,34503,false)

false : if you want the next value will be 34503

true : if you want the next value will be 34504

answered Jun 27 '16 at 09:26

Rémy Baron

1,379
8
15

score 0 · Answer 2 · edited Dec 05 '21 at 18:57

0

You should execute this command:

SELECT setval('geo_section_gid_seq', (SELECT MAX(gid) FROM 'geo_section'), true)

edited Dec 05 '21 at 18:57

cigien

57,834
11
73
112

answered Dec 05 '21 at 18:47

Ahmed

1
1

INSERT fails because primary key already exists

2 Answers2