Need help requiring uniqueness in text columns

From: Matthew Wilson <matt(at)tplus1(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Need help requiring uniqueness in text columns
Date: 2008-01-02 01:00:26
Message-ID: slrnfnloh9.nu1.matt@coleridge.tplus1.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stuart Bishop 2008-01-02 02:43:59 Re: Need help requiring uniqueness in text columns
Previous Message dterrors 2008-01-01 19:33:16 A counter argument about DISTINCT and GROUP BY in PostgreSQL