Unique Indeces and null values

[pgosse]

Hi all. I have a table with the following structure (NOTE: I'm only describing the columns affected by the indeces in question):

cms=# \d cms_access;
Table "cms_access"
    Column     | Type              | Modifiers                      
---------------+--------------------------------+---------------------------------------
 x_id          | integer           | not null default nextval('"cms_access_seq"'::text)
 a_id          | integer           | not null
 p_id          | integer           | not null
 c_id          | integer           | default null
 g_id          | integer           | not null
 u_id          | integer           | not null

x_id is the primary key, so by default has a unique index created.

I've also created a unique index on the a_id, p_id, c_id, g_id, and u_id columns.

However, I was recently able to insert two identical columns into this table (x_id = 2 and x_id = 4):

cms=# select * from cms_access;
 x_id | a_id | p_id | c_id | g_id | u_id |
------+------+------+------+------+------+
    1 |    1 |    1 |      |    1 |    1 |
    2 |    1 |    2 |      |    1 |    1 |
    4 |    1 |    2 |      |    1 |    1 |

Now, the default value for c_id is null, so I'm going to make what I think is a safe assumption in that unique indexes cannot include a column which contains a null value.

However, to get all the functionality I need, I REALLY need to have c_id be allowed to be null.

Can anyone advise me if this is indeed what's causing the problem, and if so if there is any way around it?

Thanks very much in advance,

Pablo

 

[ceco]

try this, but it is possible that the selects not always use them correct

CREATE UNIQUE INDEX xxx ON table_xx ( a_id, p_id, c_id, g_id, u_id) WHERE c_id IS NOT NULL;
CREATE UNIQUE INDEX xxx ON table_xx ( a_id, p_id, g_id, u_id) WHERE c_id IS NULL;

 

[osensei]

The rows that you have entered are unique based on your index specification. If your index key is indeed (a_id, p_id, c_id, g_id, and u_id) the no duplicate key violation should have occurred based on the data you provided. Can you provide more details as to the rules you want to apply to this table?

 

[ceco]

osensei, you are not right!

it is the normal behavior!

NULL is not equal to NULL never!

in the case with UNIQUE indexes you can read

http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=indexes-unique.html

 

[osensei]

Quite so! I guess I was thinking that x_id was the head of the index. As was noted earlier x_id is a separate primary key index.

As a practice, I typically avoid allowing nulls in indexed columns. As mentioned by ceco nulls don't behave as one might expect when being compared to oneanother. Ever wonder why a <where clause> like &quot;where column = null&quot; doesn't produce the desired result? This is why &quot;is null&quot; and &quot;is not null&quot; is provided in the SQL syntax. Try to use some other default value. After all it is only a question of semantics.

CMD