Postgres Select where arrays have equal values but no specific order

Question

Trying to query for values in a table, where two arrays have the same value, but not necessarily in the same order.

given: a column with a value of

{'first', 'second'}

expected:

SELECT * FROM my_table WHERE array_column = {'second', 'first'}

expected:

SELECT * FROM my_table WHERE array_column = {'second'}

result 1

{'first', 'second'}

result 2: nothing

I can sort the input array I am querying with, but I cannot guarantee that the database will have those arrays stored in that same order. Is there an easy way to do this?

Answer 1

Assuming the following data structure:

CREATE TABLE my_table
(
  id BIGINT PRIMARY KEY,
  array_column TEXT[]
);

INSERT INTO my_table ( id, array_column ) VALUES ( 1, ARRAY['first'] );
INSERT INTO my_table ( id, array_column ) VALUES ( 2, ARRAY['first','second'] );
INSERT INTO my_table ( id, array_column ) VALUES ( 3, ARRAY['first','second','third'] );

Combining the contains operator (@>) and the is contained by operator (<@):

SELECT
  *
FROM
  my_table
WHERE
  array_column <@ ARRAY['second','first'] AND
  array_column @> ARRAY['second','first'];

Output:

| id | array_column |
|----|--------------|
|  2 | first,second |

SQLFiddle

Answer 2

One method is to use the "contains" operators:

SELECT t.*
FROM my_table t
WHERE array_column <@ array('second', 'first') and
      array_column @> array('second', 'first')