hello,
i encountered the following problem :
mail=# \d user_preferences
Table "public.user_preferences"
Column | Type | Modifiers
----------+---------+-----------
up_uid | integer |
up_id | integer |
up_value | text |
Indexes:
"user_preferences_id_uid_uidx" unique, btree (up_id, up_uid)
"user_preferences_uid" btree (up_uid)
Triggers:
new_preferences BEFORE INSERT ON user_preferences FOR EACH ROW EXECUTE
PROCEDURE new_preferences()
mail=# reindex index user_preferences_id_uid_uidx;
ERROR: could not create unique index
DETAIL: Table contains duplicated values.
i even dropped the unique index and tried to create it again and this failed
too.
how could duplicate records appear when the unique index existed ?
even more interesting :
mail=# select * from user_preferences u where (select count(*) from
user_preferences where up_uid=u.up_uid and up_id=u.up_id)>1;
when the unique index existed this query returned nothing. after i dropped the
unique index this query returned 3 pairs of duplicates. i deleted them and
recreated the unique index and it seems to work now.
and some more: the trigger executes the following function :
BEGIN
delete from user_preferences where up_uid=NEW.up_uid and
up_id=NEW.up_id;
return NEW;
END;
so i had two methods of making sure no duplicate values could exist in this
table and both have failed somehow.
mail=# select version();
version
------------------------------------------------------------------------
PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2
thank you,
.costin
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
