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.