intarray. Функции и операторы для работы с массивами целых чисел без NULL#

В исходном дистрибутиве установлено по умолчанию: нет.

Связанные компоненты: отсутствуют.

Схема размещения: ext.

Описание#

Модуль предоставляет несколько полезных функций и операторов для работы с одномерными целочисленными массивами (1-D integer arrays). Если в массиве присутствует значение NULL, операция выдаст ошибку. Также поддерживается поиск по индексу для ряда операторов.

Модуль поддерживает проверку равенства массивов, а также индексы для операторов:

&& – пересечение;
@> – левый содержит правый;
<@ – правый содержит левый;
@@, ~~ – соответствие запросу.

Примечание:

Операторы &&, @> и <@ работают только с не содержащими NULL целочисленными массивами. Благодаря этому ограничению, в большинстве случаев, они работают быстрее, чем равнозначные одноименные встроенные операторы PostgreSQL, которые работают с массивами любых типов.

Операторы @@ и ~~ проверяют, удовлетворяет ли массив запросу, представляемому в виде значения специализированного типа данных query_int. Запрос содержит целочисленные значения, возможно с использованием операторов & (AND), | (OR) и ! (NOT). Например, запросу 1&(2|3) удовлетворяют массивы, которые содержат 1 и также содержат 2 или 3.

Выражение запроса заключается в одинарные кавычки (апострофы). Пример: '1&(2|3)'.

Функции#

Функция	Входные параметры	Возвращаемое значение	Описание
`icount`	`integer[]`	`integer`	Выдает число элементов в массиве
`sort`	`integer[], dir text`	`integer[]`	Сортирует массив в порядке возрастания или убывания в зависимости от значения параметра `dir`: `asc` – по возрастанию или `desc` – по убыванию
`sort`	`integer[]`	`integer[]`	Сортирует в порядке возрастания
`sort_asc`	`integer[]`	`integer[]`	Сортирует в порядке возрастания
`sort_desc`	`integer[]`	`integer[]`	Сортирует в порядке убывания
`uniq`	`integer[]`	`integer[]`	Удаляет стоящие рядом дубликаты
`idx`	`integer[], item integer`	`integer`	Возвращает порядковый номер (индекс) первого равного значению `item` элемента массива или `0` в случае отсутствия элемента со значением `item`
`subarray`	`integer[], start integer [, len integer`]	`integer[]`	Извлекает часть массива, которая начинается с позиции `start` и содержит `len` элементов; параметр `len` можно опустить, тогда будет извлечена часть массива, которая начинается с позиции `start` и до конца
`intset`	`integer`	`integer[]`	Создает массив с одним элементом

Операторы#

Оператор	Возвращаемое значение	Описание	Примечание
`integer[] && integer[]`	`boolean`	Пересекаются ли массивы; `true` – имеется минимум один общий элемент	–
`integer[] @> integer[]`	`boolean`	Правый массив содержится в левом	–
`integer[] <@ integer[]`	`boolean`	Левый массив содержится в правом	–
`# integer[]`	`integer`	Возвращает число элементов в массиве	Аналогичная функция – `icount`
`integer[] # integer`	`integer`	Возвращает индекс первого элемента, равного правому аргументу, или `0`, если такого элемента нет	Аналогичная функция – `idx`
`integer[] + [integer \| integer[]]`	`integer[]`	Соединение: добавляет элемент в конец массива или соединяет два массива	В качестве правого аргумента может быть целое число или массив
`integer[] - [integer \| integer[]]`	`integer[]`	Вычитание: удаляет элемент из массива или из левого массива удаляет элементы правого массива	В качестве правого аргумента может быть целое число или массив
`integer[] \| [integer \| integer[]]`	`integer[]`	Вычисляет объединение аргументов	В качестве правого аргумента может быть целое число или массив
`integer[] & integer[]`	`integer[]`	Вычисляет пересечение аргументов	–
`integer[] @@ query_int`	`boolean`	Удовлетворяет ли массив запросу; запрос представлен в виде значения специализированного типа данных `query_int`	Пример: запросу `1&(2\|3)` удовлетворяют запросы, которые содержат `1` `И` также содержат `2` `ИЛИ` `3`
`query_int ~~ integer[]`	`boolean`	Удовлетворяет ли массив запросу; коммутирующий оператор к `@@`; запрос представлен в виде значения специализированного типа данных `query_int`	Запрос содержит целочисленные значения, сравниваемые с элементами массива, возможно с использованием операторов `&` (`AND`), `\|` (`OR`) и `!` (`NOT`)

Индексы#

Модуль поддерживает индексы для операторов &&, @>, <@ и @@, а также проверку равенства массивов.

Модуль предоставляет два параметризованных класса операторов GiST:

gist__int_ops (используется по умолчанию) подходит для небольших по размеру наборов данных (массивов); этот класс аппроксимирует набор целых чисел в виде массива диапазонов; в его параметре numranges можно задать максимальное число диапазонов в одном ключе индекса (параметр необязательный, целочисленный и может принимать значения от 1 до 253, по умолчанию равен 100); при увеличении числа составляющих ключ индекса GiST массивов поиск работает точнее из-за того, что сканируется меньшая область в индексе и меньше страниц массива, но сам индекс при этом становится больше;
gist__intbig_ops применяет сигнатуру большего размера и подходит для индексации больших наборов данных (массивов), то есть содержащих много различных значений массива столбцов; этот класс аппроксимирует набор целых чисел в виде сигнатуры битовой карты; в его параметре siglen можно задать размер сигнатуры в байтах (параметр необязательный, целочисленный и может принимать значения от 1 до 2024, по умолчанию равен 16); при увеличении размера сигнатуры поиск работает точнее из-за того, что сканируется меньшая область в индексе и меньше страниц массива, но сам индекс становится больше;
gin__int_ops – нестандартный класс операторов GIN, поддерживающий те же операторы.

Доработка#

Доработка не проводилась.

Ограничения#

Функциональность предназначена для обработки одномерных целочисленных массивов. В случае передачи на вход массива большей размерности значения будут считываться как из линейного массива в порядке хранения.

Если в массиве присутствует значение NULL, операция выдаст ошибку.

Установка#

Модуль считается «доверенным», поэтому его могут устанавливать пользователи, имеющие право CREATE в текущей базе данных:

CREATE EXTENSION intarray SCHEMA ext;

Настройка#

Настройка не требуется.

Использование модуля#

Примечание

Значения массива можно вводить разными способами, которые являются идентичными:

'{1,2,3,5}'::integer[];

array [1,2,3,5].

В примерах будут использоваться оба способа.

Функции#

icount:

SELECT icount('{1,223,303,5,33}'::integer[]);

Другой способ представления массива:

SELECT icount(array [1,223,303,5,33]);

Пример вывода:

 icount 
--------
      5
(1 row)

sort:

Пример в порядке убывания:

SELECT sort('{1,223,303,5,33}'::integer[], 'desc');

Пример вывода:

       sort       
------------------
 {303,223,33,5,1}
(1 row)

Пример в порядке возрастания без параметра dir:

SELECT sort(array[1,223,303,5,33]);

Пример вывода:

       sort       
------------------
 {1,5,33,223,303}
(1 row)

sort_asc:

SELECT sort_asc (array[1,223,303,5,33]);

Пример вывода:

     sort_asc     
------------------
 {1,5,33,223,303}
(1 row)

sort_desc:

SELECT sort_desc (array[1,223,303,5,33]);

Пример вывода:

    sort_desc     
------------------
 {303,223,33,5,1}
(1 row)

uniq:

SELECT uniq('{1,1,223,303,5,5,332,2,2,2,3,1,1}'::integer[]);

Пример вывода:

          uniq           
-------------------------
 {1,223,303,5,332,2,3,1}
(1 row)

Использование в сочетании с функцией sort для удаления всех дубликатов:

SELECT uniq(sort('{1,1,1,223,303,5,5,332,2,2,2,3,1,1}'::integer[]));

Пример вывода:

         uniq          
-----------------------
 {1,2,3,5,223,303,332}
(1 row)

idx:

Элемент со значением item присутствует в массиве:

SELECT idx(array[1,223,303,5,33], 5);

Пример вывода:

 idx 
-----
   4
(1 row)

Элемента со значением item нет в массиве:

SELECT idx(array[1,223,303,5,33], 6);

Пример вывода:

 idx 
-----
   0
(1 row)

subarray:

Извлечение части массива, начиная с позиции 3 длиной в 4 элемента:

SELECT subarray('{1,2,3,4,5,4,3}'::integer[], 3, 4);

Пример вывода:

 subarray  
-----------
 {3,4,5,4}
(1 row)

Применение функции без указания параметра длины извлекаемой части массива len:

SELECT subarray('{1,2,3,4,5,4,3}'::integer[], 3);

Пример вывода:

 subarray  
-----------
 {3,4,5,4,3}
(1 row)

intset:

SELECT intset(233);

Пример вывода:

 intset 
--------
 {233}
(1 row)

Операторы#

Пересечение:

Есть пересечение:

SELECT '{1,2,3,4,5,4,3}'::integer[] && '{1,4,6}'::integer[];

Пример вывода:

 ?column? 
----------
 t
(1 row)

Нет пересечения:

SELECT '{1,2,3,4,5,4,3}'::integer[] && '{12,33}'::integer[];

Пример вывода:

 ?column? 
----------
 f
(1 row)

Содержание одного массива в другом:

Правый массив содержится в левом:

SELECT '{1,2,3,4,5,4,3}'::integer[] @> '{4,5,4}'::integer[];

Пример вывода:

 ?column? 
----------
 t
(1 row)

Левый массив содержится в правом:

SELECT '{2,3,8}'::integer[] <@ '{1,2,3,4,5,4,3}'::integer[];

Пример вывода:

 ?column? 
----------
 f
(1 row)

Добавить элемент в конец массива:

SELECT '{14,54}'::integer[] + 34;

Пример вывода:

  ?column?  
------------
 {14,54,34}
(1 row)

Соединить два массива:

SELECT '{14,54}'::integer[] + '{2,3,8}'::integer[];

Пример вывода:

   ?column?    
---------------
 {14,54,2,3,8}
(1 row)

Удалить элемент из массива:

SELECT '{14,54,34}'::integer[] - 54;

Пример вывода:

 ?column? 
----------
 {14,34}
(1 row)

Удалить элементы правого массива из левого:

SELECT '{2,2,18,3,3,66,3}'::integer[] - '{2,3,8}'::integer[];

Пример вывода:

 ?column? 
----------
 {18,66}
(1 row)

Объединение аргументов:

Объединение с элементом:

SELECT array[2,4,8,66] | 55;

Пример вывода:

   ?column?    
---------------
 {2,4,8,55,66}
(1 row)

Объединение с массивом:

SELECT array[2,4,8,66] | array[1,3,55];

Пример вывода:

     ?column?      
-------------------
 {1,2,3,4,8,55,66}
(1 row)

Пересечение аргументов:

SELECT '{2,18,3,66}'::integer[] & '{2,3,8}'::integer[];

Пример вывода:

 ?column? 
----------
 {2,3}
(1 row)

Соответствие запросу:

Проверка по запросу – оператор @@:

SELECT '{2,18,3,66}'::integer[] @@ '2&(5|6)';

Пример вывода:

 ?column? 
----------
 f
(1 row)

Проверка по запросу – оператор ~~:

SELECT '3&(18|6)' ~~ '{2,18,3,66}'::integer[];

Пример вывода:

 ?column? 
----------
 t
(1 row)

Индексы#

В примере сообщение (message) может относиться к одной или нескольким «секциям» (sections).

Следующим запросом создать таблицу, индекс с длиной сигнатуры 32 байта и наполнить тестовыми данными:

CREATE TABLE message (
   mid INT PRIMARY KEY, 
   sections INT[], 
   name text
   );
CREATE INDEX message_rdtree_idx ON message USING GIST 
            (sections gist__intbig_ops (siglen = 32));
INSERT INTO message VALUES (1, array[3,4], 'Hi, World!'), 
                           (2, array[1,4], 'Hello! World!'),
                           (3, array[1,2], 'Hi, my World!'),
                           (4, array[2,5], 'O, my World!');

Вывести сообщения из секций 1 или 2 — оператор пересечения &&:

SELECT message.mid FROM message WHERE message.sections && '{1,2}';

Пример вывода:

 mid 
-----
   2
   3
   4
(3 rows)

Вывести сообщения из секций 1 и 2 — оператор включения @>:

SELECT message.mid FROM message WHERE message.sections @> '{1,2}';

Пример вывода:

 mid 
-----
   3
(1 row)

Применить оператор запроса – query_int = 1 И 2:

SELECT message.mid FROM message WHERE message.sections @@ '1&2'::query_int;

Пример вывода:

 mid 
-----
   3
(1 row)

Применить оператор запроса – query_int = 1 ИЛИ 2:

SELECT message.mid FROM message WHERE message.sections @@ '1|2'::query_int;

Пример вывода:

 mid 
-----
   2
   3
   4
(3 rows)

Ссылки на документацию разработчика#

Дополнительно поставляемый модуль intarray: https://www.postgresql.org/docs/15/intarray.html.