On 11/13/2014 5:56 PM, Guyren Howe wrote:
On Nov 13, 2014, at 2:28 PM, Andy Colson <a...@squeakycode.net> wrote:
I want to do something that is perfectly satisfied by an hstore column. *Except* that
I want to be able to do fast (ie indexed) <, > etc comparisons, not just
equality.
From what I can tell, there isn’t really any way to get hstore to do this, so
I’ll have to go to a key-value table. But I thought I’d ask just in case I’m
missing something.
I think your missing something.
Is it one field in the hstore? Did you try an expression index?
create index cars_mph on cars ( (data->'mph') );
thats a btree index, which should support < and >. (Although I've never tried
it)
Sorry I wasn’t clear. I need a fast comparison on whatever keys someone chooses
to put in the hstore.
I’m creating a web service where you can post whatever keys and values you
wish. I am leaning toward having folks declare the relations they want to store
and the field types, so I could just create a table for each one, but I want
this to be able to scale to a lot of data for a lot of people.
Perhaps I could give everyone their own schema and just create tables for their
relations. How heavyweight are schemas?
But if I don’t want to do that, I’m leaning toward a table with a relation name
and a hstore, and then separate k-v tables for different types. I was thinking
of keeping the hstore because they will often be searching on fewer fields than
they’re retrieving, so this would avoid having to do a join for every field
they need.
Regards,
Guyren G Howe
Relevant Logic LLC
Please keep the list cc'd, there are much smarter people there.
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
