Search Postgresql Archives

Re: Unique index with Null value in one field

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux