Thread: Unique index problem

Unique index problem

From
"Sterpu Victor"
Date:
Hello
 
I created a unique index that doesn't seem to work when one column is NULL.
Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from, id_lab_sample_types);
Now I can run this insert twice and I will have 2 records in the database that seem to violate this index:
INSERT INTO lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from) VALUES(463, 9183, '2014-06-01');
 
When I create the index like this "CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from);" index works fine.
 
I tested this on postgres 9.1.4 and 9.1.9.
 
Thank you.

DISCLAIMER:
Acest mesaj de posta electronica si documentele aferente sunt confidentiale. Este interzisa distribuirea, dezvaluirea sau orice alt mod de utilizare a lor. Daca nu sunteti destinatarul acestui mesaj, este interzis sa actionati in baza acestor informatii. Citirea, copierea, distribuirea, dezvaluirea sau utilizarea in alt mod a informatiei continute in acest mesaj constituie o incalcare a legii. Daca ati primit mesajul din greseala, va rugam sa il distrugeti, anuntand expeditorul de eroarea comisa. Intrucat nu poate fi garantat faptul ca posta electronica este un mod sigur si lipsit de erori de transmitere a informatiilor, este responsabilitatea dvs. sa va asigurati ca mesajul (inclusiv documentele alaturate lui) este validat si autorizat spre a fi utilizat in mediul dvs.

Re: Unique index problem

From
Scott Marlowe
Date:
On Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor <victor@caido.ro> wrote:
> Hello
>
> I created a unique index that doesn't seem to work when one column is NULL.
> Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON
> lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from,
> id_lab_sample_types);
> Now I can run this insert twice and I will have 2 records in the database
> that seem to violate this index:
> INSERT INTO lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
> valid_from) VALUES(463, 9183, '2014-06-01');
>
> When I create the index like this "CREATE UNIQUE INDEX
> lab_tests_groups_siui_uni ON lab_tests_groups_siui(id_lab_tests_siui,
> id_lab_tests_groups, valid_from);" index works fine.
>
> I tested this on postgres 9.1.4 and 9.1.9.

This is normal operation, as one NULL is unique from other NULLS, as
far as the db is concerned. If you want it to work some other way, you
need to use a value other than null, or make an index that's something
like un


Re: Unique index problem

From
Scott Marlowe
Date:
On Sun, Dec 20, 2015 at 9:00 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor <victor@caido.ro> wrote:
>> Hello
>>
>> I created a unique index that doesn't seem to work when one column is NULL.
>> Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON
>> lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from,
>> id_lab_sample_types);
>> Now I can run this insert twice and I will have 2 records in the database
>> that seem to violate this index:
>> INSERT INTO lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
>> valid_from) VALUES(463, 9183, '2014-06-01');
>>
>> When I create the index like this "CREATE UNIQUE INDEX
>> lab_tests_groups_siui_uni ON lab_tests_groups_siui(id_lab_tests_siui,
>> id_lab_tests_groups, valid_from);" index works fine.
>>
>> I tested this on postgres 9.1.4 and 9.1.9.
>
> This is normal operation, as one NULL is unique from other NULLS, as
> far as the db is concerned. If you want it to work some other way, you
> need to use a value other than null, or make an index that's something
> like un

dangit, stupid gmail sent early.  anyway.

you'd have to make an index like unique index on (x,y,z) where field
is [not] null or something like that.

Basically NULL <> NULL <> a particular value.


Re: Unique index problem

From
Andreas Kretschmer
Date:
Sterpu Victor <victor@caido.ro> wrote:

> Hello
>
> I created a unique index that doesn't seem to work when one column is NULL.
> Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON
> lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from,
> id_lab_sample_types);
> Now I can run this insert twice and I will have 2 records in the database that
> seem to violate this index:
> INSERT INTO lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
> valid_from) VALUES(463, 9183, '2014-06-01');
>
> When I create the index like this "CREATE UNIQUE INDEX
> lab_tests_groups_siui_uni ON lab_tests_groups_siui(id_lab_tests_siui,
> id_lab_tests_groups, valid_from);" index works fine.
>
> I tested this on postgres 9.1.4 and 9.1.9.
>
> Thank you.

NULL isn't a compareable value, because of that you can't compare NULL
with NULL and so you can insert more than one NULL into the table.

You can set the column as NOT NULL or you can do something like this:

-- that's your problem
test=# create table foo (a int, b int, c int);
CREATE TABLE
test=*# create unique index on foo(a,b,c);
CREATE INDEX
test=*# insert into foo (a,b) values (1,2);
INSERT 0 1
test=*# insert into foo (a,b) values (1,2);
INSERT 0 1
test=*# rollback;
ROLLBACK

-- that's a solution
test=# create table foo (a int, b int, c int);
CREATE TABLE
test=*# create unique index on foo(coalesce(a::text,'NULL'),
coalesce(b::text,'NULL'), coalesce(c::text,'NULL'));
CREATE INDEX
test=*# insert into foo (a,b) values (1,2);
INSERT 0 1
test=*# insert into foo (a,b) values (1,2);
ERROR:  duplicate key value violates unique constraint
"foo_coalesce_coalesce1_coalesce2_idx"
DETAIL:  Key ((COALESCE(a::text, 'NULL'::text)), (COALESCE(b::text,
'NULL'::text)), (COALESCE(c::text, 'NULL'::text)))=(1, 2, NULL) already
exists.
test=*#


Maybe there are better solutions, it's a quick hack ;-)



>
>
> DISCLAIMER:

That's a public mailing list ...



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°


Re: Unique index problem

From
Andreas Kretschmer
Date:
Andreas Kretschmer <akretschmer@spamfence.net> wrote:

>
> Maybe there are better solutions, it's a quick hack ;-)

better solution:

test=*# create unique index on foo(a,b,c) where a is not null and b is
not null and c is not null;
CREATE INDEX

(partial index)


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°


Re: Unique index problem

From
Andreas Kretschmer
Date:
Andreas Kretschmer <akretschmer@spamfence.net> wrote:

> Andreas Kretschmer <akretschmer@spamfence.net> wrote:
>
> >
> > Maybe there are better solutions, it's a quick hack ;-)
>
> better solution:

sorry, doesn't work =:(


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°


Re: Unique index problem

From
Marc Mamin
Date:
____________________________________
 pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org]" im Auftrag von "Scott Marlowe
[scott.marlowe@gmail.com]
ndet: Sonntag, 20. Dezember 2015 17:02
Sterpu Victor
PostgreSQL General
eff: Re: [GENERAL] Unique index problem

un, Dec 20, 2015 at 9:00 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
 Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor <victor@caido.ro> wrote:
ello

>>> I created a unique index that doesn't seem to work when one column is NULL.
>>> Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON
>>> lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from,
>>> id_lab_sample_types);
>>> Now I can run this insert twice and I will have 2 records in the database
>>> that seem to violate this index:
>>> INSERT INTO lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
>>> valid_from) VALUES(463, 9183, '2014-06-01');
>>>
>>> When I create the index like this "CREATE UNIQUE INDEX
>>> lab_tests_groups_siui_uni ON lab_tests_groups_siui(id_lab_tests_siui,
>>> id_lab_tests_groups, valid_from);" index works fine.
>>>
>>> I tested this on postgres 9.1.4 and 9.1.9.
>>
>> This is normal operation, as one NULL is unique from other NULLS, as
>> far as the db is concerned. If you want it to work some other way, you
>> need to use a value other than null, or make an index that's something
>> like un


Hello,

> CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
valid_from,id_lab_sample_types); 

assuming that only id_lab_sample_types can be null, you could cover this with 2 partial indexes:

CREATE UNIQUE INDEX lab_tests_groups_siui_uni_a ON lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
valid_from)WHERE (id_lab_sample_types IS NULL); 
and
CREATE UNIQUE INDEX lab_tests_groups_siui_uni_b ON lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
valid_from,id_lab_sample_types) WHERE (id_lab_sample_types IS NOT NULL); 

There is a serious caveat though: queries that don't contains a "id_lab_sample_types IS [NOT] NULL" condition will
ignorethe index. 

Maybe there is also a way using DISTINCT(id_lab_tests_siui, id_lab_tests_groups, valid_from, id_lab_sample_types) in
theindex definition,  but I've never tried that and suspect the planner will also have trouble to include such an index
inthe plan. 

regards,

Marc Mamin











Re: Unique index problem

From
Jim Nasby
Date:
On 12/20/15 10:18 AM, Andreas Kretschmer wrote:
> test=*# create unique index on foo(a,b,c) where a is not null and b is
> not null and c is not null;
> CREATE INDEX

As you discovered, you'd have to build separate indexes for each of the
nullable fields:

UNIQUE ON (a,b) WHERE c IS NULL
a,c WHERE b IS NULL
b,c WHERE a IS NULL
a WHERE b IS NULL AND c IS NULL
....

That's doable for 3 fields, but obviously gets out of hand really quickly.

I wonder if it would be possible to construct an operator family
(class?) that would accept 2 rows( ie: ROW(a,b,c) ) and treat NULLs as
single values...
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


Re: Unique index problem

From
"Sterpu Victor"
Date:
Thank you.

I used the syntax with 2 indexes, it works for me.
But why does NULL != NULL?


------ Original Message ------
From: "Marc Mamin" <M.Mamin@intershop.de>
To: "Sterpu Victor" <victor@caido.ro>
Cc: "PostgreSQL General" <pgsql-general@postgresql.org>; "Andreas
Kretschmer" <akretschmer@spamfence.net>; "Scott Marlowe"
<scott.marlowe@gmail.com>
Sent: 12/20/2015 11:44:35 PM
Subject: AW: [GENERAL] Unique index problem

>
>____________________________________
>  pgsql-general-owner@postgresql.org
>[pgsql-general-owner@postgresql.org]" im Auftrag von "Scott
>Marlowe [scott.marlowe@gmail.com]
>ndet: Sonntag, 20. Dezember 2015 17:02
>Sterpu Victor
>PostgreSQL General
>eff: Re: [GENERAL] Unique index problem
>
>un, Dec 20, 2015 at 9:00 AM, Scott Marlowe <scott.marlowe@gmail.com>
>wrote:
>  Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor <victor@caido.ro> wrote:
>ello
>
>>>>  I created a unique index that doesn't seem to work when one column
>>>>is NULL.
>>>>  Index is created like this: CREATE UNIQUE INDEX
>>>>lab_tests_groups_siui_uni ON
>>>>  lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
>>>>valid_from,
>>>>  id_lab_sample_types);
>>>>  Now I can run this insert twice and I will have 2 records in the
>>>>database
>>>>  that seem to violate this index:
>>>>  INSERT INTO lab_tests_groups_siui(id_lab_tests_siui,
>>>>id_lab_tests_groups,
>>>>  valid_from) VALUES(463, 9183, '2014-06-01');
>>>>
>>>>  When I create the index like this "CREATE UNIQUE INDEX
>>>>  lab_tests_groups_siui_uni ON
>>>>lab_tests_groups_siui(id_lab_tests_siui,
>>>>  id_lab_tests_groups, valid_from);" index works fine.
>>>>
>>>>  I tested this on postgres 9.1.4 and 9.1.9.
>>>
>>>  This is normal operation, as one NULL is unique from other NULLS, as
>>>  far as the db is concerned. If you want it to work some other way,
>>>you
>>>  need to use a value other than null, or make an index that's
>>>something
>>>  like un
>
>
>Hello,
>
>>  CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON
>>lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
>>valid_from, id_lab_sample_types);
>
>assuming that only id_lab_sample_types can be null, you could cover
>this with 2 partial indexes:
>
>CREATE UNIQUE INDEX lab_tests_groups_siui_uni_a ON
>lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
>valid_from) WHERE (id_lab_sample_types IS NULL);
>and
>CREATE UNIQUE INDEX lab_tests_groups_siui_uni_b ON
>lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
>valid_from, id_lab_sample_types) WHERE (id_lab_sample_types IS NOT
>NULL);
>
>There is a serious caveat though: queries that don't contains a
>"id_lab_sample_types IS [NOT] NULL" condition will ignore the index.
>
>Maybe there is also a way using DISTINCT(id_lab_tests_siui,
>id_lab_tests_groups, valid_from, id_lab_sample_types) in the index
>definition, but I've never tried that and suspect the planner will also
>have trouble to include such an index in the plan.
>
>regards,
>
>Marc Mamin
>
>
>
>
>
>
>
>
>



Re: Unique index problem

From
Pavel Stehule
Date:


2015-12-21 7:39 GMT+01:00 Sterpu Victor <victor@caido.ro>:
Thank you.

I used the syntax with 2 indexes, it works for me.
But why does NULL != NULL?


Pavel
 

------ Original Message ------
From: "Marc Mamin" <M.Mamin@intershop.de>
To: "Sterpu Victor" <victor@caido.ro>
Cc: "PostgreSQL General" <pgsql-general@postgresql.org>; "Andreas Kretschmer" <akretschmer@spamfence.net>; "Scott Marlowe" <scott.marlowe@gmail.com>
Sent: 12/20/2015 11:44:35 PM
Subject: AW: [GENERAL] Unique index problem


____________________________________
 pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org]&quot; im Auftrag von &quot;Scott Marlowe [scott.marlowe@gmail.com]
ndet: Sonntag, 20. Dezember 2015 17:02
Sterpu Victor
PostgreSQL General
eff: Re: [GENERAL] Unique index problem

un, Dec 20, 2015 at 9:00 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
 Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor <victor@caido.ro> wrote:
ello

 I created a unique index that doesn't seem to work when one column is NULL.
 Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON
 lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from,
 id_lab_sample_types);
 Now I can run this insert twice and I will have 2 records in the database
 that seem to violate this index:
 INSERT INTO lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
 valid_from) VALUES(463, 9183, '2014-06-01');

 When I create the index like this "CREATE UNIQUE INDEX
 lab_tests_groups_siui_uni ON lab_tests_groups_siui(id_lab_tests_siui,
 id_lab_tests_groups, valid_from);" index works fine.

 I tested this on postgres 9.1.4 and 9.1.9.

 This is normal operation, as one NULL is unique from other NULLS, as
 far as the db is concerned. If you want it to work some other way, you
 need to use a value other than null, or make an index that's something
 like un


Hello,

 CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from, id_lab_sample_types);

assuming that only id_lab_sample_types can be null, you could cover this with 2 partial indexes:

CREATE UNIQUE INDEX lab_tests_groups_siui_uni_a ON lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from) WHERE (id_lab_sample_types IS NULL);
and
CREATE UNIQUE INDEX lab_tests_groups_siui_uni_b ON lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from, id_lab_sample_types) WHERE (id_lab_sample_types IS NOT NULL);

There is a serious caveat though: queries that don't contains a "id_lab_sample_types IS [NOT] NULL" condition will ignore the index.

Maybe there is also a way using DISTINCT(id_lab_tests_siui, id_lab_tests_groups, valid_from, id_lab_sample_types) in the index definition, but I've never tried that and suspect the planner will also have trouble to include such an index in the plan.

regards,

Marc Mamin












--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Unique index problem

From
"Sterpu Victor"
Date:
Seems to be working also with a single index, like this:
CREATE UNIQUE INDEX lab_tests_groups_siui_uni_c ON lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from, coalesce(id_lab_sample_types, 0));
 
------ Original Message ------
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Sterpu Victor" <victor@caido.ro>
Cc: "Marc Mamin" <M.Mamin@intershop.de>; "PostgreSQL General" <pgsql-general@postgresql.org>; "Andreas Kretschmer" <akretschmer@spamfence.net>; "Scott Marlowe" <scott.marlowe@gmail.com>
Sent: 12/21/2015 8:44:14 AM
Subject: Re: [GENERAL] Unique index problem
 


2015-12-21 7:39 GMT+01:00 Sterpu Victor <victor@caido.ro>:
Thank you.

I used the syntax with 2 indexes, it works for me.
But why does NULL != NULL?


Pavel
 

------ Original Message ------
From: "Marc Mamin" <M.Mamin@intershop.de>
To: "Sterpu Victor" <victor@caido.ro>
Cc: "PostgreSQL General" <pgsql-general@postgresql.org>; "Andreas Kretschmer" <akretschmer@spamfence.net>; "Scott Marlowe" <scott.marlowe@gmail.com>
Sent: 12/20/2015 11:44:35 PM
Subject: AW: [GENERAL] Unique index problem


____________________________________
 pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org]&quot; im Auftrag von &quot;Scott Marlowe [scott.marlowe@gmail.com]
ndet: Sonntag, 20. Dezember 2015 17:02
Sterpu Victor
PostgreSQL General
eff: Re: [GENERAL] Unique index problem

un, Dec 20, 2015 at 9:00 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
 Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor <victor@caido.ro> wrote:
ello

 I created a unique index that doesn't seem to work when one column is NULL.
 Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON
 lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from,
 id_lab_sample_types);
 Now I can run this insert twice and I will have 2 records in the database
 that seem to violate this index:
 INSERT INTO lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
 valid_from) VALUES(463, 9183, '2014-06-01');

 When I create the index like this "CREATE UNIQUE INDEX
 lab_tests_groups_siui_uni ON lab_tests_groups_siui(id_lab_tests_siui,
 id_lab_tests_groups, valid_from);" index works fine.

 I tested this on postgres 9.1.4 and 9.1.9.

 This is normal operation, as one NULL is unique from other NULLS, as
 far as the db is concerned. If you want it to work some other way, you
 need to use a value other than null, or make an index that's something
 like un


Hello,

 CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from, id_lab_sample_types);

assuming that only id_lab_sample_types can be null, you could cover this with 2 partial indexes:

CREATE UNIQUE INDEX lab_tests_groups_siui_uni_a ON lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from) WHERE (id_lab_sample_types IS NULL);
and
CREATE UNIQUE INDEX lab_tests_groups_siui_uni_b ON lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from, id_lab_sample_types) WHERE (id_lab_sample_types IS NOT NULL);

There is a serious caveat though: queries that don't contains a "id_lab_sample_types IS [NOT] NULL" condition will ignore the index.

Maybe there is also a way using DISTINCT(id_lab_tests_siui, id_lab_tests_groups, valid_from, id_lab_sample_types) in the index definition, but I've never tried that and suspect the planner will also have trouble to include such an index in the plan.

regards,

Marc Mamin












--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


DISCLAIMER:
Acest mesaj de posta electronica si documentele aferente sunt confidentiale. Este interzisa distribuirea, dezvaluirea sau orice alt mod de utilizare a lor. Daca nu sunteti destinatarul acestui mesaj, este interzis sa actionati in baza acestor informatii. Citirea, copierea, distribuirea, dezvaluirea sau utilizarea in alt mod a informatiei continute in acest mesaj constituie o incalcare a legii. Daca ati primit mesajul din greseala, va rugam sa il distrugeti, anuntand expeditorul de eroarea comisa. Intrucat nu poate fi garantat faptul ca posta electronica este un mod sigur si lipsit de erori de transmitere a informatiilor, este responsabilitatea dvs. sa va asigurati ca mesajul (inclusiv documentele alaturate lui) este validat si autorizat spre a fi utilizat in mediul dvs.

Re: Unique index problem

From
Scott Marlowe
Date:
On Sun, Dec 20, 2015 at 11:39 PM, Sterpu Victor <victor@caido.ro> wrote:
> Thank you.
>
> I used the syntax with 2 indexes, it works for me.
> But why does NULL != NULL?

Because NULL literally means "an unknown, possibly unknowable value."

You need to stop thinking of NULL as A value. It is not.


Re: Unique index problem

From
John McKown
Date:
On Mon, Dec 21, 2015 at 1:00 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Sun, Dec 20, 2015 at 11:39 PM, Sterpu Victor <victor@caido.ro> wrote:
> Thank you.
>
> I used the syntax with 2 indexes, it works for me.
> But why does NULL != NULL?

Because NULL literally means "an unknown, possibly unknowable value."

You need to stop thinking of NULL as A value. It is not.

​Right. It would be sort of like asking: "is an object which I can't see the same as (==) or different from (!=) some other object which I can't see?" Answer: "How would I know???"​

 

--
Computer Science is the only discipline in which we view adding a new wing to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown