Сравнить массивы для равенства, игнорируя порядок элементов

Ответ 1

Простейшая вещь - сортировать их и сравнивать их сортировку. См. сортировка массивов в PostgreSQL.

Данные образца:

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

самое лучшее, что нужно сделать, - если строки массива всегда целые, это использовать расширение intarray, поскольку объясняет Эрвин в своем ответе. Это намного быстрее, чем любая формулировка pure-SQL.

В противном случае для общей версии, которая работает для любого типа данных, определите 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';

и используйте его сортировку и сравнение отсортированных массивов:

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

Существует важное оговорка:

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

будет ложным. Это может быть или не быть тем, что вы хотите, в зависимости от ваших намерений.

В качестве альтернативы определите функцию 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;

а затем:

SELECT array_compare_as_set(ids, signed_ids) FROM aa;

Это немного отличается от сравнения двух значений array_sort ed. array_compare_as_set устраняет дубликаты, делая array_compare_as_set(ARRAY[1,2,3,3],ARRAY[1,2,3]) true, тогда как array_sort(ARRAY[1,2,3,3]) = array_sort(ARRAY[1,2,3]) будет ложным.

Оба этих подхода будут иметь довольно плохую производительность. Учтите, что вы всегда храните свои массивы, отсортированные в первую очередь.

Ответ 2

Работа с массивами целых вы можете установить расширение intarray. p >

Установите один раз для каждой базы данных (в Postgres 9.1 или новее):

CREATE EXTENSION intarray;

Тогда вы можете просто:

SELECT uniq(sort(ids)) = uniq(sort(signed_ids));

Или:

SELECT ids @> signed_ids AND ids <@ signed_ids;

Смелый акцент на функциях и операторах из intarray. Оба выражения будут игнорировать порядок и двуличие элементов. Подробнее об этих функциях и операторах в полезном руководстве здесь.

Примечания:

• Операторы
• intarray работают только для массивов integer, а не bigint или smallint или любого другого типа данных.
• Вы можете использовать операторы сдерживания @> и <@ без установки intarray, потому что в стандартном распределении Postgres существуют общие варианты для типов массивов. intarray устанавливает только специализированные операторы только для int[], которые обычно быстрее.

• В отличие от общих операторов, теги intarray не принимают значения NULL в массивах, что может сбивать с толку: теперь вы получаете сообщение об ошибке, если у вас есть NULL в любом вовлеченном массиве.
  Если вам нужно работать со значениями NULL, вы можете по умолчанию стандартным, общим операторам с помощью схемы-квалификации оператора с конструкцией OPERATOR:

  SELECT ARRAY[1,4,null,3]::int[] OPERATOR([email protected]>) ARRAY[3,1]::int[]

• Общие операторы не могут использовать индексы с классом оператора intarray и наоборот.

Ответ 3

Вы можете использовать содержащийся оператор:

(array1 < @array2 и array1 @ > array2)