2

For example, I store interests for Joe as chemistry and classical-music (as two separate values) , and you can query the table like where interests in ('chemistry', 'biochem') and it will use the index to efficiently find people such as Joe.

Does PostgreSQL have such a datatype that can index multiple values to allow advanced queries? What are the performance advantages for queriues? And what are the important caveats?

Jesvin Jose
  • 22,498
  • 32
  • 109
  • 202
  • 2
    Why don't you normalize your design? Storing comma separated values is almost never a good idea. –  Sep 25 '12 at 09:39
  • I understand its bad in traditional DBs which dont index multiple values. But I just felt DBs must offer this feature. But I did update this question about caveats thanks to you :-) – Jesvin Jose Sep 25 '12 at 10:05
  • 1
    The question is misleading/poorly worded. You're asking if it can index the values in a text field containing comma-separated values. – Dondi Michael Stroma Sep 25 '12 at 14:22
  • I think the caveat is that it's not a good design. If you want to use a RDBMS, then I second other people recommendation to create a proper, relational schema to store the data. People have been working for decades to create very efficient ways to do this kind of querying (via a separate "interests" table). PostgreSQL is quite good at it. Otherwise, I suggest you evaluate different storage engines. – David S Sep 25 '12 at 19:46
  • @DondiMichaelStroma, corrected the poor wording – Jesvin Jose Sep 26 '12 at 07:00

2 Answers2

3

If you use an array datatype, it can be indexed and the index can be used, check this topic: Can PostgreSQL index array columns?

A better datamodel might be a better idea, but that's up to you.

Community
  • 1
  • 1
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
1

You could use the hstore datatype for this. You would only store keys, not values. A hstore column can be indexed and testing for the presence of a key is really fast.

Something like:

create table person 
(
   person_id integer not null primary key,
   name text not null,
   interests hstore
);

insert into person (person_id, name, interests)
values 
(1, 'Joe', 'chemistry => NULL, biochem => null'::hstore);

Then to find a person with a specific interest you can use:

select *
from person
where interests ? 'chemistry'
   or interests ? 'biochem';

It's a bit of a hack because the hstore datatype is intended to store key/value pairs and in this case you only need the keys.