Question: Postgres Match All array values to same column with and condition

Question

Postgres Match All array values to same column with and condition

Answers 2
Added at 2016-12-14 11:12
Tags
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]);
Answers to

Postgres Match All array values to same column with and condition

nr: #1 dodano: 2016-12-14 11:12

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
nr: #2 dodano: 2016-12-14 11:12

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)

Source Show
◀ Wstecz