Re: Insert in table with UNIQUE index

On Wed, Jan 27, 2016 at 5:30 AM, Artem Tomyuk <admin(at)leboutique(dot)com> 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.