0

I've table city

CREATE TABLE city
(
  id serial NOT NULL,
  name character varying(255) NOT NULL,
  country character varying(30) DEFAULT NULL::character varying,
  CONSTRAINT city_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE city
  OWNER TO uks;

CREATE UNIQUE INDEX uniq_2d5b02345373c966
  ON city
  USING btree
  (country COLLATE pg_catalog."default");

CREATE UNIQUE INDEX uniq_2d5b02345e237e06
  ON city
  USING btree
  (name COLLATE pg_catalog."default");

Now when i insert to table everything is ok and sequence is increased. When i try to add row with existing name i've got error because uniqe key name, this is what i expect but in this case sequence is increased too :/

uks=> SELECT * FROM city;
 id | name | country 
----+------+---------
(0 rows)

uks=> INSERT INTO city (name) VALUES('ONE');
INSERT 0 1
uks=> INSERT INTO city (name) VALUES('TWO');
INSERT 0 1
uks=> SELECT * FROM city;
 id | name | country 
----+------+---------
  1 | ONE  | 
  2 | TWO  | 
(2 rows)

uks=> INSERT INTO city (name) VALUES('TWO');
ERROR:  duplicate key value violates unique constraint "uniq_2d5b02345e237e06"
DETAIL:  Key (name)=(TWO) already exists.
uks=> INSERT INTO city (name) VALUES('TWO');
ERROR:  duplicate key value violates unique constraint "uniq_2d5b02345e237e06"
DETAIL:  Key (name)=(TWO) already exists.
uks=> INSERT INTO city (name) VALUES('TWO');
ERROR:  duplicate key value violates unique constraint "uniq_2d5b02345e237e06"
DETAIL:  Key (name)=(TWO) already exists.
uks=> INSERT INTO city (name) VALUES('THREE');
INSERT 0 1
uks=> SELECT * FROM city;
 id | name  | country 
----+-------+---------
  1 | ONE   | 
  2 | TWO   | 
  6 | THREE | 
(3 rows)

Is this normal behavior in postgres? I think, that when error occours sequence shouldn't be increased.

nicram
  • 353
  • 3
  • 7
  • 24
  • Thanks, this "solves" my problem ;/ – nicram Jan 22 '17 at 09:52
  • 1
    Yes, this is by design. See section labelled **Important** in the manual: https://www.postgresql.org/docs/current/static/functions-sequence.html –  Jan 22 '17 at 14:28

0 Answers0