Re: indexing elements of a csv ?

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: pgsql-general-owner(at)postgresql(dot)org [pgsql-general-owner(at)postgresql(dot)org] on behalf of Gauthier, Dave [dave(dot)gauthier(at)intel(dot)com]
Sent: Tuesday, March 12, 2013 8:50 AM
To: pgsql-general(at)postgresql(dot)org
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 !