Thread: Unique index with Null value in one field

Unique index with Null value in one field

From

Hrishi Joshi

Date:

11 October 2005, 22:37:03

Hi,

I need to define a Unique index on 3 non-PK fields (composite key) on my
table in PostgreSQL 8.0.3.

The problem is, if any of those 3 fields is Null, PostgreSQL allows
duplicate rows to be inserted. While searching through archives, I found
more information about this.

But I need to know how can I make PostgreSQL throw error on attempt to
insert second record having same 3 field values, one of them being Null.


------------------------------------------------
myid |  field1 |  field2 |  field3 | description
PK   |  <---  Unique Index --->    |
------------------------------------------------
100  | ABC     | XYZ     | <null>  | Record 1   -> This is ok.
101  | ABC     | XYZ     | <null>  | Record 2   -> * This should error!
------------------------------------------------

Fields {field1, field2, field3} have unique index on them and "myid" is
the primary key of my table.


Oracle 9i throws exception in such case, but PostgreSQL does not.


Thanks,
- Hrishi Joshi.

Re: Unique index with Null value in one field

From

Tom Lane

Date:

12 October 2005, 02:51:55

Hrishi Joshi <hjoshi@abcsinc.com> writes:
> I need to define a Unique index on 3 non-PK fields (composite key) on my
> table in PostgreSQL 8.0.3.

> The problem is, if any of those 3 fields is Null, PostgreSQL allows
> duplicate rows to be inserted.

That is the behavior defined by the SQL standard.

> But I need to know how can I make PostgreSQL throw error on attempt to
> insert second record having same 3 field values, one of them being Null.

You can't.  Rethink your data representation, instead.  You are misusing
NULL if you think that it represents something unique.

            regards, tom lane

Re: Unique index with Null value in one field

From

Stephan Szabo

Date:

12 October 2005, 03:48:19

On Tue, 11 Oct 2005, Hrishi Joshi wrote:

> I need to define a Unique index on 3 non-PK fields (composite key) on my
> table in PostgreSQL 8.0.3.
>
> The problem is, if any of those 3 fields is Null, PostgreSQL allows
> duplicate rows to be inserted. While searching through archives, I found
> more information about this.
>
> But I need to know how can I make PostgreSQL throw error on attempt to
> insert second record having same 3 field values, one of them being Null.
>
>
> ------------------------------------------------
> myid |  field1 |  field2 |  field3 | description
> PK   |  <---  Unique Index --->    |
> ------------------------------------------------
> 100  | ABC     | XYZ     | <null>  | Record 1   -> This is ok.
> 101  | ABC     | XYZ     | <null>  | Record 2   -> * This should error!
> ------------------------------------------------
>
> Fields {field1, field2, field3} have unique index on them and "myid" is
> the primary key of my table.
>
>
> Oracle 9i throws exception in such case, but PostgreSQL does not.

We're pretty sure that the standard UNIQUE constraint requires this
behavior and our unique index is the mechanism for checking that
constraint and so has the same behavior.

If there's a non-null value that you know can't be there, you might be
able to use a unique index on something like
(coalesce(field1, <v>)), (coalesce(field2,<v>)), (coalesce(field3,<v>))

Otherwise, you might be able to use a set of partial unique indexes for
the null cases. I'm not sure how many it would require, though.

Re: Unique index with Null value in one field

From

Jaime Casanova

Date:

12 October 2005, 22:28:30

On 11 Oct 2005 17:36:59 -0500, Hrishi Joshi <hjoshi@abcsinc.com> wrote:
> Hi,
>
> I need to define a Unique index on 3 non-PK fields (composite key) on my
> table in PostgreSQL 8.0.3.
>
> The problem is, if any of those 3 fields is Null, PostgreSQL allows
> duplicate rows to be inserted. While searching through archives, I found
> more information about this.
>
> But I need to know how can I make PostgreSQL throw error on attempt to
> insert second record having same 3 field values, one of them being Null.
>
>
> ------------------------------------------------
> myid |  field1 |  field2 |  field3 | description
> PK   |  <---  Unique Index --->    |
> ------------------------------------------------
> 100  | ABC     | XYZ     | <null>  | Record 1   -> This is ok.
> 101  | ABC     | XYZ     | <null>  | Record 2   -> * This should error!
> ------------------------------------------------
>
> Fields {field1, field2, field3} have unique index on them and "myid" is
> the primary key of my table.
>
>
> Oracle 9i throws exception in such case, but PostgreSQL does not.
>
>
> Thanks,
> - Hrishi Joshi.
>
>

maybe with a function and comparing yourself...

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Re: Unique index with Null value in one field

From

Chris Travers

Date:

20 October 2005, 21:53:09

Hrishi Joshi wrote:

>Hi,
>
>I need to define a Unique index on 3 non-PK fields (composite key) on my
>table in PostgreSQL 8.0.3.
>
>The problem is, if any of those 3 fields is Null, PostgreSQL allows
>duplicate rows to be inserted. While searching through archives, I found
>more information about this.
>
>But I need to know how can I make PostgreSQL throw error on attempt to
>insert second record having same 3 field values, one of them being Null.
>
>
>------------------------------------------------
>myid |  field1 |  field2 |  field3 | description
>PK   |  <---  Unique Index --->    |
>------------------------------------------------
>100  | ABC     | XYZ     | <null>  | Record 1   -> This is ok.
>101  | ABC     | XYZ     | <null>  | Record 2   -> * This should error!
>
>
Why?  PostgreSQL knows that we cannot determine whether these records
are different or not.  See the discussion on what NULL means...

IMO, people who use NULL to mean "not applicable" are misusing the
value. Not applicable should always be equal to not applicable, but it
is always unknown whether unknown is equal to another unknown.  I would
suggest using a different table for that column if it is not always
applicable.  The typical example is:

You have 500 employees.  Some employees have salaries, some have wages.
Some people might:

create table employee(
employee_id serial,
ssn varchar not null,
...
wage numeric,
salary numeric
);

but here NULL could either mean "unknown" or "not applicable" so we
don't really know which is which and it can create data management issues.

In this case it is better to:

create table employee(
employee_id serial,
ssn varchar not null,
...
);
create table wage (
employee_id,
wage numeric
);
create table salary (
employee_id,
salary numeric
);

>------------------------------------------------
>
>Fields {field1, field2, field3} have unique index on them and "myid" is
>the primary key of my table.
>
>
>
>Oracle 9i throws exception in such case, but PostgreSQL does not.
>
>
You could use a custom trigger, or a custom function and a functional
index....  Or even an index on COALESCE...

But Oracle's handling of NULL's is broken, especially in string fields
(varchar, char, etc) because it wrongly assumes that an empty string and
NULL are equivalent.  Search for prior discussions on this list....

Best Wishes,
Chris Travers
Metatron Technology Consulting