Re: indexing elements of a csv ?

2013/3/12 Gauthier, Dave <dave(dot)gauthier(at)intel(dot)com>:
> 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.

A GIN index might do the trick:

CREATE INDEX ix_col1_ix ON foo USING GIN(string_to_array(col1,','));

(This is assuming the CSV values can be cleanly converted to
an array using "string_to_array()").

You could then query it with:
SELECT col0 FROM foo WHERE string_to_array(col1,',') @> '{bbb}'::text[];

HTH

Ian Barwick