Postgres Match All array values to same column with and condition ---updated

Question

I have table table_a with following columns

id  event_id
1   101
1   102
1   103
2   105
2   103
2   106

I and to search (101, 103) with and conditions similar to IN query with OR condition

For example id 1 match both for 101 and 103 event_id;

For this I write following query but it not working.

select * from table_a where event_id = ALL(ARRAY[101,103]);

UPDATED--------------------------- And I have another problem

let say id is foreign of another table event_categories having relation like this.

id      parent_id
101     null
102     101
103     101
104     null
105     104

so I want to fetch records from table_a based on AND with parent event category, OR within sub event categories of that parent.

For example 101, 104 with AND 102, 103 within OR of 101

If you have a new problem, ask a new question (and mark one of the answers as accepted, so that this question is marked as resolved) — user330315
– user330315, Commented Dec 21, 2016 at 7:11

user330315 · Accepted Answer · 2016-12-14 10:44:45Z

10

You need to aggregate all event_ids for a single ID:

select id
from table_a
group by id
having array_agg(event_id) @> array[101,103];

The @> is the contains operator so it checks if the array of all event_ids contains the array with those two IDs.

That will return any id that has at least the two event 101 and 103 (which is what you asked for in your question).

If you would like to find those IDs that have exactly those two event_ids (which your sample data does not contain) you could use:

select id
from table_a
group by id
having array_agg(distinct event_id order by event_id) = array[101,103];

Note that the order of the elements in the array matters for the = operator (unlike the "contains" @> operator)

answered Dec 14, 2016 at 10:44

user330315

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

1 Comment

Thanks for detail explanation.

sagi · Accepted Answer · 2016-12-14 10:44:37Z

2

Use the HAVING clause:

SELECT t.id 
FROM YourTable
WHERE event_id IN(101,103)
GROUP BY t.id
HAVING COUNT(distinct event_id) = 2

answered Dec 14, 2016 at 10:44

sagi

40.6k7 gold badges66 silver badges87 bronze badges