PostgreSQL check if array contains any element from left-hand array

arrays postgresql set-intersection
Lander picture Lander · Feb 13, 2014 · Viewed 21.4k times · Source

I know that in PostgreSQL you can run a query like:

SELECT (1 = ANY('{1,3,4,7}'::int[])) AS result to check if the right-hand array contains the element 1. I was wondering if there is an easy way to check if the right-hand array contains any element from the left-hand array. Something like:

SELECT ('{2,3}'::int[] = ANY('{1,3,4,7}'::int[])) AS result

Is there an easy way to do this without iterating over the left-hand loop myself?

Answer

Craig Ringer picture Craig Ringer · Feb 13, 2014

Sure, use the && array-overlaps operator:

SELECT ARRAY[1,2] && ARRAY[1,3,4,7];

See array functions and operators.

Related questions

Check if value exists in Postgres array

Using Postgres 9.0, I need a way to test if a value exists in a given array. So far I came up with something like this: select '{1,2,3}'::int[] @> (ARRAY[]::int[] || value_variable::int) But I keep thinking there …

Read More
How to turn a json array into rows in postgres

I have a json array stored in my postgres database. The json looks like this: [ { "operation": "U", "taxCode": "1000", "description": "iva description", "tax": "12" }, { "operation": "U", "taxCode": "1001", "description": "iva description", "tax": "12" }, { "operation": "U", "taxCode": "1002", "description": "iva description", "tax": "12" } ] Now I need to …

Read More
Loop over array dimension in plpgsql

In plpgsql, I want to get the array contents one by one from a two dimension array. DECLARE m varchar[]; arr varchar[][] := array[['key1','val1'],['key2','val2']]; BEGIN for m in select arr LOOP raise NOTICE …

Read More