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" <[email protected]>
To: "Sterpu Victor" <[email protected]>
Cc: "Marc Mamin" <[email protected]>; "PostgreSQL General"
<[email protected]>; "Andreas Kretschmer"
<[email protected]>; "Scott Marlowe" <[email protected]>
Sent: 12/21/2015 8:44:14 AM
Subject: Re: [GENERAL] Unique index problem
2015-12-21 7:39 GMT+01:00 Sterpu Victor <[email protected]>:
Thank you.
I used the syntax with 2 indexes, it works for me.
But why does NULL != NULL?
because it was designed
http://www.w3schools.com/sql/sql_null_values.asp
Pavel
------ Original Message ------
From: "Marc Mamin" <[email protected]>
To: "Sterpu Victor" <[email protected]>
Cc: "PostgreSQL General" <[email protected]>; "Andreas
Kretschmer" <[email protected]>; "Scott Marlowe"
<[email protected]>
Sent: 12/20/2015 11:44:35 PM
Subject: AW: [GENERAL] Unique index problem
____________________________________
[email protected]
[[email protected]]" im Auftrag von "Scott
Marlowe [[email protected]]
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 <[email protected]>
wrote:
Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
