From: | Ian Lawrence Barwick <barwick(at)gmail(dot)com> |
---|---|
To: | "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: indexing elements of a csv ? |
Date: | 2013-03-12 15:02:20 |
Message-ID: | CAB8KJ=gAMQ+6qP1=TYAAQh4HTeYq-Fe-b89drngDbKdfPU64jQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Erickson | 2013-03-12 15:19:17 | Re: indexing elements of a csv ? |
Previous Message | Gauthier, Dave | 2013-03-12 13:50:28 | indexing elements of a csv ? |