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?

Quote from the manual "Arrays are not sets; searching for specific array elements can be a sign of database misdesign." — user330315
– user330315, Commented Jun 11, 2018 at 15:41
You are probably looking for the overlaps operator &&: postgresql.org/docs/current/static/functions-array.html — user330315
– user330315, Commented Jun 11, 2018 at 15:41
@a_horse_with_no_name I was thinking about just using the overlap operator, and then doing exact comparison in code but I wasn't sure if Postgres offers a way to do this through a query. — Patrick Zawadzki
– Patrick Zawadzki, Commented Jun 11, 2018 at 15:43

Lacobus · Accepted Answer · 2018-06-11 18:29:50Z

7

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

answered Jun 11, 2018 at 18:29

Lacobus

1,65815 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-06-11 16:02:50Z

1

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

answered Jun 11, 2018 at 16:02

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Postgres Select where arrays have equal values but no specific order

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related