indexing elements of a csv ?

From: "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: indexing elements of a csv ?
Date: 2013-03-12 13:50:28
Message-ID: 0AD01C53605506449BA127FB8B99E5E161156ED8@FMSMSX105.amr.corp.intel.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 !

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ian Lawrence Barwick 2013-03-12 15:02:20 Re: indexing elements of a csv ?
Previous Message JotaComm 2013-03-12 13:21:07 Problems with PostgreSQL Replication (Log Shipping)