Postgres - Comparing Two Arrays

 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"}');

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

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

PostgreSQL: Compare Two Arrays Is Any One Value Exists In Second Array

Operator && should do the trick. This will return if any of elements in Array1 is present in Array2.
Refer to PostgreSQL document here: Array Operators

select array[1,2,3] && array[2,4,5], array[1,2,3] && array[4,5, 6], array[1,2,3] && array[1]
Output: true, false, true

If you are looking if all the elements present in second array, you should use @> or <@

Compare arrays for equality, ignoring order of elements

The simplest thing to do is sort them and compare them sorted. See sorting arrays in PostgreSQL.

Given sample data:

CREATE TABLE aa(ids integer[], signed_ids integer[]);
INSERT INTO aa(ids, signed_ids) VALUES (ARRAY[1,2,3], ARRAY[2,1,3]);

the best thing to do is to if the array entries are always integers is to use the intarray extension, as Erwin explains in his answer. It's a lot faster than any pure-SQL formulation.

Otherwise, for a general version that works for any data type, define an array_sort(anyarray):

CREATE OR REPLACE FUNCTION array_sort(anyarray) RETURNS anyarray AS $$
SELECT array_agg(x order by x) FROM unnest($1) x;
$$ LANGUAGE 'SQL';

and use it sort and compare the sorted arrays:

SELECT array_sort(ids) = array_sort(signed_ids) FROM aa;

There's an important caveat:

SELECT array_sort( ARRAY[1,2,2,4,4] ) = array_sort( ARRAY[1,2,4] );

will be false. This may or may not be what you want, depending on your intentions.

---

Alternately, define a function array_compare_as_set:

CREATE OR REPLACE FUNCTION array_compare_as_set(anyarray,anyarray) RETURNS boolean AS $$
SELECT CASE
  WHEN array_dims($1) <> array_dims($2) THEN
    'f'
  WHEN array_length($1,1) <> array_length($2,1) THEN
    'f'
  ELSE
    NOT EXISTS (
        SELECT 1
        FROM unnest($1) a 
        FULL JOIN unnest($2) b ON (a=b) 
        WHERE a IS NULL or b IS NULL
    )
  END
$$ LANGUAGE 'SQL' IMMUTABLE;

and then:

SELECT array_compare_as_set(ids, signed_ids) FROM aa;

This is subtly different from comparing two array_sorted values. array_compare_as_set will eliminate duplicates, making array_compare_as_set(ARRAY[1,2,3,3],ARRAY[1,2,3]) true, whereas array_sort(ARRAY[1,2,3,3]) = array_sort(ARRAY[1,2,3]) will be false.

Both of these approaches will have pretty bad performance. Consider ensuring that you always store your arrays sorted in the first place.

How to compare two array rows of same table in Postgres?

I am a strong advocate of representing this type of data using a junction/association table. Usually, the SQL queries can be better optimized.

But given your data structure, you can generate all the pairs of employees and then count the departments where they are the same. Assuming there are no duplicate departments for a given employee (as in your list):

with t as (
      select v.*
      from (values (1000, array['acct', 'hr']), (1005, array['dev', hr'])) v(empid, depts)
     )
select t1.empid, t2.empid,
       (select count(*)
        from unnest(t1.depts) d1 join
             unnest(t2.depts) d2
             on d1 = d2
       ) cnt
from t t1 join
     t t2
     on t1.empid < t2.empid;

Here is a db<>fiddle.

---

Related Topics