Thread: Overlap function for hstore?
Hi, I'd like to get an overlap function similar to '&&' but for key-value pairs of hstore! This underfits: postgres=# select hstore_to_array('a=>1,b=>2,c=>3'::hstore) && hstore_to_array('a=>2,d=>4,b=>2'::hstore) ...because array overlaps op takes every element (even 'a' or 2 alone) and doesn't test for key and value together like in 'b=>2'! Any clever ideas? Yours, Stefan
> This underfits: > > postgres=# select hstore_to_array('a=>1,b=>2,c=>3'::hstore) && > hstore_to_array('a=>2,d=>4,b=>2'::hstore) > > ...because array overlaps op takes every element (even 'a' or 2 alone) > and doesn't test for key and value together like in 'b=>2'! How about hstore_to_matrix? Then you have a tuple for each key/value pair. Paul
Hi,
I'd like to get an overlap function similar to '&&' but for key-value
pairs of hstore!
This underfits:
postgres=# select hstore_to_array('a=>1,b=>2,c=>3'::hstore) &&
hstore_to_array('a=>2,d=>4,b=>2'::hstore)
...because array overlaps op takes every element (even 'a' or 2 alone)
and doesn't test for key and value together like in 'b=>2'!
Any clever ideas?
Not tested!
SELECT hstore(array_agg(key), array_agg(value)) FROM (
SELECT each(...)
INTERSECT
SELECT each(...)
) src
David J.
Hi David 2015-04-04 3:16 GMT+02:00 David G. Johnston <david.g.johnston@gmail.com>: > Not tested! > > SELECT hstore(array_agg(key), array_agg(value)) FROM ( > SELECT each(...) > INTERSECT > SELECT each(...) > ) src > > David J. Thanks! SELECT hstore(array_agg((tmp_attr).key), array_agg((tmp_attr).value)) FROM ( SELECT each((select 'a=>1,b=>2,c=>3'::hstore)) as tmp_attr INTERSECT ALL SELECT each((select 'a=>2,d=>4,b=>2'::hstore)) as tmp_attr ) tmp_table; Cheers, S, 2015-04-04 3:16 GMT+02:00 David G. Johnston <david.g.johnston@gmail.com>: > On Fri, Apr 3, 2015 at 5:37 PM, Stefan Keller <sfkeller@gmail.com> wrote: >> >> Hi, >> >> I'd like to get an overlap function similar to '&&' but for key-value >> pairs of hstore! >> >> This underfits: >> >> postgres=# select hstore_to_array('a=>1,b=>2,c=>3'::hstore) && >> hstore_to_array('a=>2,d=>4,b=>2'::hstore) >> >> ...because array overlaps op takes every element (even 'a' or 2 alone) >> and doesn't test for key and value together like in 'b=>2'! >> >> Any clever ideas? > > > Not tested! > > SELECT hstore(array_agg(key), array_agg(value)) FROM ( > SELECT each(...) > INTERSECT > SELECT each(...) > ) src > > David J. >