Thread: hstore, but with fast range comparisons?
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 Ithought I’d ask just in case I’m missing something.
On 11/13/2014 3:46 PM, Guyren Howe wrote: > I want to do something that is perfectly satisfied by an hstore column. *Except* that I want to be able to do fast (ieindexed) <, > 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. ButI 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) -Andy
Andy Colson wrote > On 11/13/2014 3:46 PM, Guyren Howe 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) With the one caveat that everything in hstore is a string so you'd probably want to add an appropriate cast to the expression. David J. -- View this message in context: http://postgresql.nabble.com/hstore-but-with-fast-range-comparisons-tp5826886p5826898.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: service allowing arbitrary relations was Re: hstore, but with fast range comparisons?
From
Andy Colson
Date:
On 11/13/2014 5:56 PM, Guyren Howe wrote: > On Nov 13, 2014, at 2:28 PM, Andy Colson <andy@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 (ieindexed) <, > 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 declarethe relations they want to store and the field types, so I could just create a table for each one, but I want thisto 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 tablesfor different types. I was thinking of keeping the hstore because they will often be searching on fewer fields thanthey’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