Re: Insert in table with UNIQUE index

[bricklen <bricklen@xxxxxxxxx>]

On Wed, Jan 27, 2016 at 5:30 AM, Artem Tomyuk <admin@xxxxxxxxxxxxxx> wrote:

I have a table with unique index with 2 exactly the same rows.

How it can be possible?

CREATE TABLE _inforgchngr6716_test

(

  _nodetref bytea NOT NULL,

  _noderref bytea NOT NULL,

  _messageno numeric(10,0)

)

CREATE UNIQUE INDEX _inforg6716_bynodemsg_rn_test

  ON _inforgchngr6716_test

  USING btree

  (_nodetref, _noderref, _messageno);

In the duplicated entries, are any of the "_messageno" values NULL? If so, that would explain it. NULL is not bound by the constraint, you have a few options: make it NOT NULL, COALESCE() it to a known value, or create a couple partial indexes to enforce the uniqueness.