Professional Web Applications Themes

Adding UNIQUE constraint on NULL column - PostgreSQL / PGSQL

I am trying to add a unique constraint on a column that can be null. The doentation 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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings...

  1. #1

    Default Adding UNIQUE constraint on NULL column

    I am trying to add a unique constraint on a column that can be null. The
    doentation 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


    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings

    Dave Smith Guest

  2. #2

    Default Re: Adding UNIQUE constraint on NULL column

    Dave Smith <dave.smithcandata.com> writes:
    > I am trying to add a unique constraint on a column that can be null. The
    > doentation 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

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to [email]majordomopostgresql.org[/email] so that your
    message can get through to the mailing list cleanly

    Doug McNaught Guest

  3. #3

    Default Re: Adding UNIQUE constraint on NULL column

    Yes
    On Thu, 2005-01-13 at 10:12, Doug McNaught wrote:
    > Dave Smith <dave.smithcandata.com> writes:
    >
    > > I am trying to add a unique constraint on a column that can be null. The
    > > doentation 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


    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Dave Smith Guest

  4. #4

    Default Re: Adding UNIQUE constraint on NULL column

    Dave Smith <dave.smithcandata.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

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    Doug McNaught Guest

  5. #5

    Default Re: Adding UNIQUE constraint on NULL column

    On Thu, Jan 13, 2005 at 09:01:08 -0500,
    Dave Smith <dave.smithcandata.com> wrote:
    > I am trying to add a unique constraint on a column that can be null. The
    > doentation 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.

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

    Bruno Wolff III Guest

  6. #6

    Default Re: Adding UNIQUE constraint on NULL column

    On Thu, 2005-01-13 at 11:02, Doug McNaught wrote:
    > Dave Smith <dave.smithcandata.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?

    [url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

    Scott Marlowe Guest

  7. #7

    Default Re: Adding UNIQUE constraint on NULL column

    -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    Or look at:

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

    (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.smithcandata.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?
    >
    > [url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]
    >
    >
    - -----------------------------------------------------------
    Frank D. Engel, Jr. <fde101fjrhome.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 [url]www.doteasy.com[/url]


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

    Frank D. Engel, Jr. Guest

Similar Threads

  1. Adding Constraint
    By wallace reis in forum MySQL
    Replies: 2
    Last Post: December 15th, 07:27 PM
  2. Replies: 1
    Last Post: May 16th, 05:24 AM
  3. Syntax for UNIQUE constraint?
    By Guinness Mann in forum ASP
    Replies: 1
    Last Post: October 15th, 09:58 PM
  4. Unique constraint
    By Aaron Bertrand - MVP in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 06:05 PM
  5. How to ensure if column A is null, column B has to be null
    By Bill in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 07:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139