#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    11
    Rep Power
    0

    UNIQUE NULL rows


    Hey guys. I have a big problem. I have this table:

    Code:
    CREATE TABLE foo
    (
      id serial NOT NULL,
      a integer NOT NULL,
      b integer NOT NULL,
      c text,
      CONSTRAINT foo_pkey PRIMARY KEY (id),
      CONSTRAINT foo_a_b_c_key UNIQUE (a, b, c)
    );
    So I want the triple (a, b, c) to be unique. This does not work if c is NULL though.

    For example, this works:

    Code:
    id    a     b     c
    
    1     1     2     NULL
    2     1     2     NULL
    Does anyone know a way to solve this problem without assigning c a value (like coalesce, because I want to distinguish between an empty value and NULL)?

    What I mean with the last sentence is this:

    Code:
    create unique index idx_notnull on foo (a, b, coalesce(c, ''));
    This works, but that way i can't distinguish between NULL and an empty string.

    Thanks for your help.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    NULL is never equal to anything.

    If you want to make a and b unique when C is NULL, then you can do exactly that: create two partial unique constraints, one for WHERE c IS NULL and one for WHERE C IS NOT NULL.

    See: http://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    11
    Rep Power
    0
    Thanks Vinny42. That was exactly what I was looking for

IMN logo majestic logo threadwatch logo seochat tools logo