postgres - comparing two arrays

Question

postgres has an array data type, in this case a numeric array:

CREATE TABLE sal_emp (name text, pay_by_quarter integer[]);
INSERT INTO sal_emp VALUES ('one', '{1,2,3}');
INSERT INTO sal_emp VALUES ('two', '{4,5,6}');
INSERT INTO sal_emp VALUES ('three', '{2,4,6}');
SELECT * FROM sal_emp;

Result:
one, {1,2,3}
two, {4,5,6}
three, {2,4,6}

From what i can tell, you can only query an array as follows:

SELECT * FROM sal_emp WHERE 4=ANY(pay_by_quarter);
SELECT * FROM sal_emp WHERE ARRAY[4,5,6]=pay_by_quarter;

which means you can select a row with the array contains a match for a single argument, or if the whole array matches an array argument.

I need to select a row where any member of the row's array matches any member of an argument array - kind of like an 'IN' but i can't figure out how. I've tried the following two approaches but neither work:

SELECT * from sal_emp WHERE ARRAY[4,5,6]=ANY(pay_by_quarter);
SELECT * from sal_emp WHERE ANY(pay_by_quarter) IN (4,5,6);

I assume i could do something with converting the array to a string but that sounds like poor solution..

any ideas?

Answer 1

figured it ... there's an && operator

http://www.postgresql.org/docs/current/static/functions-array.html

"&& overlap (have elements in common) ARRAY[1,4,3] && ARRAY[2,1]"

Answer 2

For comparing arrays in Postgress, there are array operators.
Find the Operators section here: https://www.postgresql.org/docs/current/functions-array.html

A quick way to compare two arrays is by doing so,

( (ARRAY_1 @> ARRAY_2) AND (ARRAY_1 <@ ARRAY_2) )

which basically checks if all the unique values in ARRAY_2 is present in ARRAY_1 AND vice versa. Don't forget to use both the checks/conditions/comparison in conjunction(AND)

These are called containment operators which checks all the unique values in one array and compares it with the other. They are also called as bird-operators because the operator @> AND <@ resembles the head of a bird.

The way it works is as follows:

ARRAY_1 <@ ARRAY_2
// Check/Direction: Left-to-Right
// Checks if all the unique elements in ARRAY_1 is present in ARRAY_2 as well. If yes, it will return TRUE
// Please note this will return TRUE even if ARRAY_2 has extra elements that are not present in ARRAY_1

Similarly,

ARRAY_1 @> ARRAY_2
// Note we have changed the operator to '@>' from '<@'. Check/Direction: Right-to-Left
// Checks if all the unique elements in ARRAY_2 is present in ARRAY_1 as well. If yes, it will return TRUE
// Please note this will return TRUE even if ARRAY_1 has extra elements that are not present in ARRAY_2

So even if the order of elements are different it, will still work.

A simple way to not get confused and remember this is by looking at the > OR < sign.
The ARRAY(unique elements) which has the less-than sign pointed is being compared with the ARRAY(unique elements) with greater-than sign pointed.
So the ARRAY which has the greater-than sign pointed can contain extra unique elements, and still return TRUE.

Let us try some examples

SELECT array[1, 2, 3] @> array[1, 2, 3] AS RESULT
!-- Returns TRUE (Check is Right-to-Left)

SELECT array[1, 2, 3] @> array[3, 1, 2] AS RESULT
!-- Returns TRUE (Check is Right-to-Left)

SELECT array[1, 2, 3, 3, 3, 3] @> array[1, 2, 3] AS RESULT
!-- Returns TRUE (Check is Right-to-Left)

SELECT array[1, 2, 3, 3, 3, 3] <@ array[3, 2, 1, 3, 2, 5] AS RESULT
!-- Returns TRUE (Check is Left-to-Right)

SELECT array[1, 2, 3, 4] <@ array[1, 2, 3] AS RESULT
!-- Returns FALSE (Check is Left-to-Right)

SELECT array[1, 2, 3, 4] @> array[1, 2, 3] AS RESULT
!-- Returns TRUE (Check is Right-to-Left)

SELECT array[1, 2, 3] <@ null AS RESULT
!-- Returns empty (Check is Left-to-Right)

SELECT array[1, 2, 3] @> null AS RESULT
!-- Returns empty (Check is Right-to-Left)

SELECT null <@ null AS RESULT
!-- Doesn't work if both the values are null - Error: Operator is not unique: unknown <@ unknown

SELECT array[1, 2, 3] <@ array[] AS RESULT
!-- Doesn't work on empty arrays - Error: Cannot determine type of empty array

SELECT array[] <@ array[] AS RESULT
!-- Doesn't work on empty arrays - Error: Cannot determine type of empty array

Please note

• This is used for comparing arrays.
• Works by extracting unique values from both the arrays - and then compares them.
• It does not check the count of elements in the ARRAY. i.e it can return TRUE even if both the arrays are of different length ([1, 2] @> [1] OR [1, 1] @> [1] are still TRUE)
• Doesn't work if both the values are NULL
• Doesn't work if either of the array is empty ([])

Answer 3

To compare elements of an array with elements of another array for at least one match you can use && operator. It will return true only if at least one element from each array match.

Though right answer to this question is already here for decades I am adding this example for better clarity to beginners.

Schema:

 create table mytable (id int, value text[]);
 insert into mytable values (1,'{"dog", "cat", "fish"}');
 insert into mytable values (2,'{"elephant", "mouse"}');
 insert into mytable values (3,'{"lizard", "dog", "parrot"}');
 insert into mytable values (4,'{"bear", "bird", "cat"}');

Query:

 select * from mytable where array['dog', 'cat']&&(value);

Output:

id	value
1	{dog,cat,fish}
3	{lizard,dog,parrot}
4	{bear,bird,cat}

db<>fiddle here

Query#2 (match array element using wildcard characters)

To compare array elements using wildcard characters you can use unnest() parse every element of both arrays and compare them using Like operator.

   SELECT    distinct m.*
   FROM  mytable m, unnest(value)b 
   inner join  unnest(array['dog%', 'ca%']) a 
   on b LIKE a 

Output:

id	value
1	{dog,cat,fish}
3	{lizard,dog,parrot}
4	{bear,bird,cat}

db<>fiddle here

Answer 4

Trying not to plagiarise pstanton's answer, but clarifying a use case.

To have a condition that is satisfied by any of two (or more) columns having any of a list of values:

 select * from mytable
 where array(col1, col2) && array('foo', 'bar', baz')