CODEWITHSUNDEEP
sqlpostgresql
Patrick Zawadzki
Patrick Zawadzki

Reputation: 466

Postgres Select where arrays have equal values but no specific order

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?

Upvotes: 4

Views: 3239

Answers (2)

Lacobus
Lacobus

Reputation: 1658

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

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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')

Upvotes: 1

Related Questions