Hello IDEA 3: Use two hash functions: CREATE UNIQUE INDEX uidx ON TEST((decode(md5(a),'hex')),(hashtext(a))); removing spaces helps CREATE UNIQUE INDEX uidx ON test((decode(md5(lower(replace(a,' ',''))),'hex'))); Regards Pavel Stehule CREATE UNIQUE INDEX nodups on MESSAGE (my_timestamp_col, md5(my_text_col)); > On 02/01/2008, Matthew Wilson <matt@xxxxxxxxxx> wrote: > I have a table MESSAGE with a text column and a timestamp column. I > want to make sure that I have no rows with duplicates for both values. > I have two ideas right now for how to do this. > > IDEA 1: > > CREATE UNIQUE INDEX nodups on MESSAGE (my_timestamp_col, my_text_col); > > IDEA 2: > > CREATE UNIQUE INDEX nodups on MESSAGE (my_timestamp_col, md5(my_text_col)); > > I am speculating that an index on the md5 is cheaper than on a text > column. I'm willing to risk the chance of a hash collision. > > I don't want to use this index to allow searching inside the text > column. I just want to protect against duplication. > > Are either of these approaches any good? What are other ways to > guarantee uniqueness for the pair of my timestamp column and my text > column? > > TIA > > Matt > > > -- > Programming, economics, gardening, life in Cleveland. > http://blog.tplus1.com > > > ---------------------------(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 > ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
