Обсуждение: Adding UNIQUE constraint on NULL column
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
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
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
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.
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
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.
-----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