Postgresql – Search one array element with another

arraypostgresql

I have got tables:

children_tab
id
name
lastname

parent_tab
id
name
lastname

children_parent_tab
children_id
parent_id

There is also parent view that return in field children_ids elements from children_parent_tab

I have to search parents by children ids

Let's say that parent contain:

1|name1|lastname1|{1}
2|name2|lastname2|{5}
3|name3|lastname3|{3,7}

SELECT * FROM parent p WHERE ARRAY[1,2,3,4,5]::int[]  @> children_ids

will return only parent with id 1 and 2 but without that one with id 3. How that query can be changed to return also parent with id 3 because I'm looking for parent of child with id 3?

Best Answer

You seem to run the query on the view. Should be substantially faster based on underlying tables. Like:

SELECT DISTINCT parent_id
FROM   children_parent_tab
WHERE  children_id = ANY ('{1,2,3,4,5}'::int[]);

Details depend on undisclosed exact table definitions and requirements.

Be that as it may, you can fix the query you presented with the "overlap" operator && instead of the "contains" operator @>:

SELECT * FROM parent p WHERE ARRAY[1,2,3,4,5]::int[] && children_ids;

This way you get all parents of any of the children in the given array.