Need help requiring uniqueness in text columns

Поиск
Список
Период
Сортировка
От Matthew Wilson
Тема Need help requiring uniqueness in text columns
Дата
Msg-id slrnfnloh9.nu1.matt@coleridge.tplus1.com
обсуждение исходный текст
Ответы Re: Need help requiring uniqueness in text columns  (Stuart Bishop <stuart@stuartbishop.net>)
Re: Need help requiring uniqueness in text columns  ("Pavel Stehule" <pavel.stehule@gmail.com>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Andrew Sullivan
Дата:
Сообщение: Re: basic questions: Postgres with yum on CentOS 5.1
Следующее
От: Stuart Bishop
Дата:
Сообщение: Re: Need help requiring uniqueness in text columns