From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "Matthew Wilson" <matt(at)tplus1(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Need help requiring uniqueness in text columns |
Date: | 2008-01-02 09:23:46 |
Message-ID: | 162867790801020123h7d7c2516udd100cd0632f6db6@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)tplus1(dot)com> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2008-01-02 11:39:08 | Re: group by and count(*) behaviour in 8.3 |
Previous Message | Edoardo Panfili | 2008-01-02 09:20:07 | group by and count(*) behaviour in 8.3 |