Re: Managing Key Value tags on rows

On Mon, Nov 17, 2014 at 3:43 PM, Tim Uckun <timuckun(at)gmail(dot)com> wrote:
> My Main worry is that the tag values will be of different types and ideally
> I would be able to search for using type specific ranges. For example if the
> tag value is a date then be able to do a date interval search but if the tag
> values are strings then do an ilike search.
>
> I was thinking of creating different columns for different types so that I
> can do a search like 'WHERE tag_name = 'blah' and date_value between ....' .
> In other words I would have a string_value, integer_value, numeric_value,
> date_value ... columns.

Yeah -- noted, you're solidly in the EAV camp here, at least for 9.3.
Personally, I'd just use a single value column unless indexing 'value'
for range searches was very important:

create table foo_attribute
(
foo_id references foo on delete cascade,
type text, -- text,bool, int, etc
key text,
value text
);

without that you can just involve a cast to get what you want: select
* from foo_attribute where value::int between ...

Even with jsonb, EAV remains the most flexible (although not the best)
approach. jsonb with jsquery gives incredibly fast searching but it
doesn't handle partial string matching at all -- at least the last
time I looked.

merlin