NULL values seem to short-circuit my unique index - Mailing list 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