[GENERAL] NULL values seem to short-circuit my unique index

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general