The following bug has been logged online: Bug reference: 3965 Logged by: Juho Saarikko Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3RC2 Operating system: Linux Description: UNIQUE constraint fails on long column values Details:
It is impossible to add an UNIQUE constraint which includes columns with long values. The reason seems to be that UNIQUE is implemented using b-tree index, which cannot handle values longer than 8191 bytes. While I didn't test, I'd imagine that this would also mean that any attempt to insert such values to an already unique column would fail. It is propably impossible to fix this in a simple way, since it is an inherent result of the underlying storage specification rather than a mere programming error, so the documentation needs to be updated to warn about this. I suggest implementing unique hash indexes and automatically creating one (and turning the b-tree index into a non-unique one) when a large value is inserted to fix this. Alternatively, fix b-trees so they can handle large values; however, a hash index should be far more efficient for this specific case, since the size of a hash is independent of pre-hash data size. Exact error message: ****************** kuvat=# alter table pictures ADD constraint pic_unique unique (safe); NOTICE: 00000: ALTER TABLE / ADD UNIQUE will create implicit index "pic_unique" for table "pictures" LOCATION: DefineIndex, indexcmds.c:434 ERROR: 54000: index row requires 47148 bytes, maximum size is 8191 LOCATION: index_form_tuple, indextuple.c:170 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
