Thread: Adding UNIQUE constraint on NULL column

Adding UNIQUE constraint on NULL column

From
Dave Smith
Date:
I am trying to add a unique constraint on a column that can be null. The
documentation states that null is treated as non equal values but I want
them to be equal. Is there another way of doing this other than writing
a before insert trigger?

--
Dave Smith
CANdata Systems Ltd
416-493-9020


Re: Adding UNIQUE constraint on NULL column

From
Doug McNaught
Date:
Dave Smith <dave.smith@candata.com> writes:

> I am trying to add a unique constraint on a column that can be null. The
> documentation states that null is treated as non equal values but I want
> them to be equal. Is there another way of doing this other than writing
> a before insert trigger?

UNIQUE constraints on NULLable columns work fine.  It's not clear from
the above what you're looking for.

Are you really saying that you want 'NULL = NULL' to return 't'?

-Doug

Re: Adding UNIQUE constraint on NULL column

From
Dave Smith
Date:
Yes
On Thu, 2005-01-13 at 10:12, Doug McNaught wrote:
> Dave Smith <dave.smith@candata.com> writes:
>
> > I am trying to add a unique constraint on a column that can be null. The
> > documentation states that null is treated as non equal values but I want
> > them to be equal. Is there another way of doing this other than writing
> > a before insert trigger?
>
> UNIQUE constraints on NULLable columns work fine.  It's not clear from
> the above what you're looking for.
>
> Are you really saying that you want 'NULL = NULL' to return 't'?
>
> -Doug
--
Dave Smith
CANdata Systems Ltd
416-493-9020


Re: Adding UNIQUE constraint on NULL column

From
Bruno Wolff III
Date:
On Thu, Jan 13, 2005 at 09:01:08 -0500,
  Dave Smith <dave.smith@candata.com> wrote:
> I am trying to add a unique constraint on a column that can be null. The
> documentation states that null is treated as non equal values but I want
> them to be equal. Is there another way of doing this other than writing
> a before insert trigger?

I think you will need an after trigger to enforce that.
You might also consider using some other value than NULL. If other tables
are going to reference this one using the unique column, potentially having
a NULL could be a problem.

Re: Adding UNIQUE constraint on NULL column

From
Doug McNaught
Date:
Dave Smith <dave.smith@candata.com> writes:

> On Thu, 2005-01-13 at 10:12, Doug McNaught wrote:

>> Are you really saying that you want 'NULL = NULL' to return 't'?

> Yes

Well, that's not how NULL works.

-Doug

Re: Adding UNIQUE constraint on NULL column

From
Scott Marlowe
Date:
On Thu, 2005-01-13 at 11:02, Doug McNaught wrote:
> Dave Smith <dave.smith@candata.com> writes:
>
> > On Thu, 2005-01-13 at 10:12, Doug McNaught wrote:
>
> >> Are you really saying that you want 'NULL = NULL' to return 't'?
>
> > Yes
>
> Well, that's not how NULL works.

In this instance, just create an artificial NULL, like a text string of
"NONE" and insert that.



Re: Adding UNIQUE constraint on NULL column

From
"Frank D. Engel, Jr."
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Or look at:

http://archives.postgresql.org/pgsql-sql/2003-08/msg00286.php

(and possibly its follow-up)

On Jan 13, 2005, at 3:16 PM, Scott Marlowe wrote:

> On Thu, 2005-01-13 at 11:02, Doug McNaught wrote:
>> Dave Smith <dave.smith@candata.com> writes:
>>
>>> On Thu, 2005-01-13 at 10:12, Doug McNaught wrote:
>>
>>>> Are you really saying that you want 'NULL = NULL' to return 't'?
>>
>>> Yes
>>
>> Well, that's not how NULL works.
>
> In this instance, just create an artificial NULL, like a text string of
> "NONE" and insert that.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>
>
- -----------------------------------------------------------
Frank D. Engel, Jr.  <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB5uqc7aqtWrR9cZoRAmrYAJ9xn7/4BJSDIkV2HKa0LuTaH3dkawCfXwe+
LOlaK7dCVRjsx+InLCaxkwA=
=paot
-----END PGP SIGNATURE-----



___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com