Junkone wrote:
HI
I have a table with a unique key constraint on col1,col2. The dattabase
allowed an multiple inserts. here is how
col1 col2
'abc' <empty space>
'abc' <empty space>
I suppose the values in col2 are NULL values?
You cannot compare NULL values. NULL == NULL evaluates to NULL (not true
or false), you have no way of knowing those two records are equal.
PostgreSQL (among others) assumes NULL values to always be different.
The meaning and interpretation of NULL is a frequent topic of discussion.
I am not sure how this can happen
You can solve your problem by creating 2 unique constraints:
CREATE UNIQUE INDEX idx1 ON table (col1, col2) WHERE col2 IS NOT NULL;
CREATE UNIQUE INDEX idx2 ON table (col1) WHERE col2 IS NULL;
Regards,
--
Alban Hertroys
[EMAIL PROTECTED]
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
