Query on multiple postgres hstores combined with or

Question

This is a hardcoded example of what I'm trying to achieve:

SELECT id FROM places
    WHERE metadata->'route'='Route 23'
    OR  metadata->'route'='Route 22'
    OR  metadata->'region'='Northwest'
    OR  metadata->'territory'='Territory A';

Metadata column is an hstore column and I'm wanting to build up the WHERE clause dynamically based on another query from a different table. The table could either be:

    id   |        metadata           
---------+----------------------------
    1647 | "region"=>"Northwest"
    1648 | "route"=>"Route 23"
    1649 | "route"=>"Route 22"
    1650 | "territory"=>"Territory A"

or

    id |    key     |    value    
----+-------------+-------+---
    1  |   route     | Route 23
    2  |   route     | Route 22
    3  |   region    | Northwest
    4  |   territory | Territory A

Doesnt really matter, just whatever works to build up that where clause. It could potentially have 1 to n number of OR's in it based on the other query.

stsmurf · Accepted Answer · 2015-02-27 21:56:28Z

1

Ended up with a solution using the 2nd table (distribution table):

    id   |        metadata           
---------+----------------------------
    1647 | "region"=>"Northwest"
    1648 | "route"=>"Route 23"
    1649 | "route"=>"Route 22"
    1650 | "territory"=>"Territory A"

Used the following join, which the @> sees if the places.metadata contains the distributions.metadata

SELECT places.id, places.metadata
    FROM places INNER JOIN distributions
    ON places.metadata @> distributions.metadata
    WHERE distributions.some_other_column = something;

edited Feb 27, 2015 at 21:56

user330315

answered Feb 27, 2015 at 21:44

stsmurf

4825 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query on multiple postgres hstores combined with or

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related