Re: Functional Index Question

On Wed, Mar 12, 2008 at 11:46:12AM -0400, James B. Byrne wrote:
> CREATE UNIQUE INDEX idxUF_table_column ON table
> (lower(trim(both ' ' from(regexp_replace(<column>, /( ){2,}/g," " )))))
> What I intend this to do is to squeeze out excess whitespace, strip off
> leading and trailing blanks, and then force the whole thing to lowercase.
> Is this idea worth pursuing and, if it is, is my regexp correct for the
> purpose intended?

if you'd try the query you would see instantly:
# select lower(trim(both ' ' from(regexp_replace(' depeSz hub ', /( ){2,}/g," " ))));
ERROR: syntax error at or near "/"
LINE 1: ...im(both ' ' from(regexp_replace(' depeSz hub ', /( ){2,}/g...

correct way:
select lower(trim(both ' ' from(regexp_replace(' depeSz hub ', E'\\s+', ' ', 'g' ))));

now. i would suggest *not* to use this as base for index.

make a wrapper function istead:

create function cleaned(text) returns text as $BODY$
select lower(trim(both ' ' from(regexp_replace($1, E'\\s+', ' ', 'g' ))));
$BODY$ language sql immutable;

now you can simply:
create unique index xxx on table ( cleaned(<column>) );
plus your sql's will look saner.
instead of:
select * from table where lower(trim(both ' ' from(regexp_replace(<column>, E'\\s+', ' ', 'g' )))) = lower(trim(both ' ' from(regexp_replace('some_string', E'\\s+', ' ', 'g' ))));
you will have:
select * from table where cleaned(<field>) = cleaned('some_string');

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)