Re: Unique Index - Mailing list pgsql-general

>I actually just wanted to know if there is a way around this problem.
>Obviously it is implemented that way for whatever reason.
>
> I still though think some arguments given in some of the replies, while
> probably correct, are besides the point.

Sorry. I was hoping someone else would answer.
>
> I use a unique index that may contain null values. On an insert or update
> I can now not rely on the exception thrown but actually have to write a
> select statement to check if the same row exists, which I believe defies
> ONE purpose of having unique indices. Whether Null is associated with
> "unknown value", "divided by zero"... or however one wants to interpret it
> is not the issue here, in my view NULL in the same column have the same
> value or at least should be treated the same. (If I want to differentiate
> the state, I would use a code instead of NULL as a NULL does not give any
> indication of its meaning, thus we could safely assume they are treated as
> equal).
>
> Maybe there could be an option in the creation of the index to indicate on
> how to use NULL values.

I can think of two options.

One was mentioned already. If only one row can have a null value then it
seems to me that you should make it a non null and null would have a special
code.

If it really needs to be null. Then a rather messy solution would be to have
a second column (I'll call it a null indicator) which can only be 1 or null
and have a unique index on it.

colA        ind
------       -----
1             null
2             null
3             null
null         1

>
> How do other DBMS handle this?

Oracle is the same.
>
> A
>
>
>
>
>
> Tom Lane wrote:
>
>>"Dann Corbit" <DCorbit@connx.com> writes:
>>
>>>Or (perhaps better yet, violating trichotomy) ...
>>>If <Some_column> has a null numeric value, then ALL of the following are
>>>FALSE for that case:
>>>
>>
>>
>>>Some_column < 0
>>>Some_column > 0
>>>Some_column = 0 Some_column <> 0 // This is the one that many find
>>>surprising
>>>Some_column <= 0
>>>Some_column >= 0
>>>
>>
>>It's worse than that: the above do *not* yield FALSE, they yield NULL.
>>Which does act like FALSE in a simple WHERE clause, but there are other
>>cases (like CHECK clauses) where it doesn't.  "x NOT IN (SELECT ...)"
>>is a case that newbies routinely get bitten by.
>>
>>
>>>Even at that, I think that being able to insert more than one null value
>>>into a unique index should be considered as a bug (or diagnosed as an
>>>error).
>>>
>>
>>Direct your complaints to the ISO SQL standards committee.
>>
>> regards, tom lane
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 8: explain analyze is your friend
>>
>>
>>
>
>
>