An option would be to create a column of type tsvector. That way you could do
text searches using partial words or words and get results including those
containing forms of the word.
________________________________
From: [email protected] [[email protected]]
on behalf of Gauthier, Dave [[email protected]]
Sent: Tuesday, March 12, 2013 8:50 AM
To: [email protected]
Subject: [GENERAL] indexing elements of a csv ?
Hi:
v9.0.1 on linux.
I have a table with a column that is a csv. Users will select records based
upon the existence of an element of the csv. There is an index on that column
but I'm thinking that it won't be of much use in this situation. Is there a
way to facilitate these queries?
Example:
create table foo (col0 text, col1 text);
create index foo_col1 on foo (col1);
insert into foo (col0,col1) values
('moe','aa,bbb,c'),('larry','xxxxx,bbb,yyy'),('curly','m,nnnn,oo');
now...
select col0 from foo where <the csv element 'bbb' exists as a csv element of
col1>
Some attempts, which get the right answers, but which probably won't be very
efficient...
select col0 from foo where string_to_array('bbb','') <@ string_to_array(col1);
select col0 from foo where ','||col1||',' like '%,bbb,%';
select col0 from foo where ((col1 like 'bbb,%') or (col1 like '%,bbb,%') or
(col1 like '%,bbb'));
Long shot, but I thought I'd ask anyway.
Thanks in Advance !
