Skip site navigation (1) Skip section navigation (2)

NULL values seem to short-circuit my unique index

From: Matthew Wilson <matt(at)tplus1(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: NULL values seem to short-circuit my unique index
Date: 2008-09-29 02:32:56
Message-ID: slrnge0fio.b3l.matt@sprout.tplus1.com (view raw or flat)
Thread:
Lists: pgsql-general
I'm trying to comprehend how NULL values interact with unique indexes.
It seems like I can insert two rows with NULL values in a column with a
unique constraint just fine.

Is there something special about NULL?  Can anyone post some links to
explain what is going on?

Here's the example that stumped me:

I created an organization table and a category table:

    matt=# \d organization
                             Table "public.organization"
     Column |  Type   |                         Modifiers                         
    --------+---------+----------------------------------
     id     | integer | not null default
    nextval('organization_id_seq'::regclass)
     name   | text    | 
    Indexes:
        "organization_pkey" PRIMARY KEY, btree (id)

    matt=# \d category
                                   Table "public.category"
           Column       |  Type   |                       Modifiers
    --------------------+---------+-------------------------------------------------------
     organization_id    | integer |
     id                 | integer | not null default nextval('category_id_seq'::regclass)
     name               | text    |
     parent_category_id | integer |
    Indexes:
        "category_pkey" PRIMARY KEY, btree (id)
        "nodup_categories" UNIQUE, btree (organization_id, name, parent_category_id)
    Foreign-key constraints:
        "category_organization_id_fkey" FOREIGN KEY (organization_id) REFERENCES organization(id)
        "category_parent_category_id_fkey" FOREIGN KEY (parent_category_id) REFERENCES category(id)

I thought that nodup_categories index would prevent me from putting in
these values, but I was wrong:

    matt=# insert into category (organization_id, name) values (1, 'bogus');
    INSERT 0 1
    matt=# insert into category (organization_id, name) values (1, 'bogus');
    INSERT 0 1
    matt=# insert into category (organization_id, name) values (1, 'bogus');
    INSERT 0 1
    matt=# select * from category;
     organization_id | id | name  | parent_category_id
    -----------------+----+-------+--------------------
                   1 |  1 | bogus |
                   1 |  2 | bogus |
                   1 |  3 | bogus |
    (3 rows)


So, obviously there's something I'm missing.  Clearly an index exists.

What's the deal with NULL?

I think I'll use some other value besides NULL to indicate categories
with parents.  Then I would need to figure out how to handle the FK
constraint on the parent_category_id column.

Matt


Responses

pgsql-general by date

Next:From: ChrisDate: 2008-09-29 02:59:59
Subject: Re: NULL values seem to short-circuit my unique index
Previous:From: SebDate: 2008-09-29 00:31:52
Subject: Re: inserting to a multi-table view

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group