Postgres unique constraint on index does not work

Question

On a quite simple table

CREATE TABLE collectionmaster
(
  id character varying(32) NOT NULL,
  version integer,
  publisherid character varying(12),
  title character varying(1024),
  subtitle character varying(1024),
  type character varying(255) NOT NULL,
  CONSTRAINT collectionmaster_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
CREATE INDEX idx_coll_title
  ON collectionmaster
  USING btree
  (title COLLATE pg_catalog."default");

I tried to add a unique check either via unique index

CREATE UNIQUE INDEX idx_uni_coll_title
  ON collectionmaster
  USING btree
  (publisherid COLLATE pg_catalog."default", title COLLATE pg_catalog."default", (COALESCE(subtitle, 'no_sub'::character varying)) COLLATE pg_catalog."default");

or via unique constraint

ALTER TABLE collectionmaster
  ADD CONSTRAINT uni_coll_title UNIQUE(publisherid, title, subtitle);

to intercept accidentally multiple creation by a java service (spring-jpa on hibernate) executed in several threads. But strangely neither of index and constraint works as expected. Eight records are added by eight threads which are not unique according to either constraint or index in separate transaction, but no unique violations are thrown, and all records are inserted into the table. And none of the values are null, as was the problem in other questions here. The constaint was not deferred, index as constraint where valid. Postgres version is 9.2.

I am quite clueless here.

EDIT: These are the results of a query in this table, extracted from pgAdmin (hard to format it nicer here):

"id";"version";"publisherid";"title";"subtitle";"type"
"53b3df625baf40bf885b48daa366fbc8";1;"5109138";"Titel";"Untertitel";"set"
"2673ef9a33f84289995d6f7288f07b46";1;"5109138";"Titel";"Untertitel";"set"
"ef7c385205034fdc89fe39e3eca48408";1;"5109138";"Titel";"Untertitel";"set"
"527922f2f3464f91826dbae2e2b67caf";1;"5109138";"Titel";"Untertitel";"set"
"794638a725324319852d10b828257df7";1;"5109138";"Titel";"Untertitel";"set"
"dbe2201058974d63a2107131f0080233";1;"5109138";"Titel";"Untertitel";"set"
"cbb77c7c1adb415db006853a6f6244ac";1;"5109138";"Titel";"Untertitel";"set"
"0b6606fe015040fbbc85444361ab414c";1;"5109138";"Titel";"Untertitel";"set"

Even on these I can execute

insert into collectionmaster(id, version, publisherid, title, subtitle, type) values('aaa',1,'5109138','Titel','Untertitel','set')

without getting a constraint violation. I can't believe it, too, but this is the problem...

@a_horse_with_no_name Yes I am; the values in the columns are identical in every respect, coming from the same java data object. — Uwe Allner
– Uwe Allner, Commented Jan 27, 2015 at 11:01
Then please show us some sample data (ideally as INSERT statements) that reproduce this problem. Given the information you have shown this is very hard to believe. — user330315
– user330315, Commented Jan 27, 2015 at 11:06
Works for me: sqlfiddle.com/#!15/999d4/1 are you sure you created the index on the same database where you run the insert? — user330315
– user330315, Commented Jan 27, 2015 at 11:55
@a_horse_with_no_name Yes, in the same pgAdmin SQL window. And even stranger is: when I drop the constraint, and try to recreate it on the non-unique data, I get the expected error message that data prevents creation. — Uwe Allner
– Uwe Allner, Commented Jan 27, 2015 at 12:37
Are you sure your spring-jpa does not want to "fix" your schema at initialization? Like, with spring.jpa.hibernate.ddl-auto is set to update or create-drop? docs.spring.io/spring-boot/docs/current/reference/html/… I'm not sure update can drop unwanted indexes too, but could you double-check this with pgAdmin (after you started your application)? — pozs
– pozs, Commented Jan 27, 2015 at 12:38

pozs · Accepted Answer · 2015-01-27 13:09:29Z

1

Make sure your spring-jpa config spring.jpa.hibernate.ddl-auto is not set to create-drop.

That way, hibernate will always drop & re-create your whole schema, which won't contain your custom unique constraints, nor your indexes.

answered Jan 27, 2015 at 13:09

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres unique constraint on index does not work

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related