Postgres Pro Standard : Документация: 16: F.28. intarray — работа с массивами целых чисел : Компания Postgres Professional

F.28. intarray — работа с массивами целых чисел
Пред.	Наверх	Приложение F. Дополнительные модули и расширения, поставляемые в `postgrespro-std-16-contrib`	Начало	След.

F.28. intarray — работа с массивами целых чисел #

F.28.1. Функции и операторы intarray
F.28.2. Поддержка индексов
F.28.3. Пример
F.28.4. Авторы

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

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

Многие из этих операций имеют смысл только с одномерными массивами. Хотя им можно передать входной массив и большей размерности, значения будут считываться из него как из линейного массива в порядке хранения.

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

F.28.1. Функции и операторы `intarray` #

Реализованные в модуле intarray функции перечислены в Таблице F.14, а операторы — в Таблице F.15.

Таблица F.14. Функции intarray

Функция Описание Примеры
`icount` ( `integer[]` ) → `integer` Выдаёт число элементов в массиве. `icount('{1,2,3}'::integer[])` → `3`
`sort` ( `integer[]`, `dir` `text` ) → `integer[]` Сортирует массив в порядке возрастания или убывания. Направление определяется параметром `dir`, значением которого должно быть `asc` (по возрастанию) или `desc` (по убыванию). `sort('{1,3,2}'::integer[], 'desc')` → `{3,2,1}`
`sort` ( `integer[]` ) → `integer[]` `sort_asc` ( `integer[]` ) → `integer[]` Сортирует в порядке возрастания. `sort(array[11,77,44])` → `{11,44,77}`
`sort_desc` ( `integer[]` ) → `integer[]` Сортирует в порядке убывания. `sort_desc(array[11,77,44])` → `{77,44,11}`
`uniq` ( `integer[]` ) → `integer[]` Удаляет соседние дубликаты. Часто используется вместе с `sort` для удаления всех дубликатов. `uniq('{1,2,2,3,1,1}'::integer[])` → `{1,2,3,1}` `uniq(sort('{1,2,3,2,1}'::integer[]))` → `{1,2,3}`
`idx` ( `integer[]`, `item` `integer` ) → `integer` Выдаёт индекс первого элемента, равного `item`, или 0, если такого элемента нет. `idx(array[11,22,33,22,11], 22)` → `2`
`subarray` ( `integer[]`, `start` `integer`, `len` `integer` ) → `integer[]` Извлекает часть массива, которая начинается с позиции `start` и содержит `len` элементов. `subarray('{1,2,3,2,1}'::integer[], 2, 3)` → `{2,3,2}`
`subarray` ( `integer[]`, `start` `integer` ) → `integer[]` Извлекает часть массива, которая начинается с позиции `start`. `subarray('{1,2,3,2,1}'::integer[], 2)` → `{2,3,2,1}`
`intset` ( `integer` ) → `integer[]` Создаёт массив с одним элементом. `intset(42)` → `{42}`

Функция

Описание

Примеры

icount ( integer[] ) → integer

Выдаёт число элементов в массиве.

icount('{1,2,3}'::integer[]) → 3

sort ( integer[], dir text ) → integer[]

Сортирует массив в порядке возрастания или убывания. Направление определяется параметром dir, значением которого должно быть asc (по возрастанию) или desc (по убыванию).

sort('{1,3,2}'::integer[], 'desc') → {3,2,1}

sort ( integer[] ) → integer[]

sort_asc ( integer[] ) → integer[]

Сортирует в порядке возрастания.

sort(array[11,77,44]) → {11,44,77}

sort_desc ( integer[] ) → integer[]

Сортирует в порядке убывания.

sort_desc(array[11,77,44]) → {77,44,11}

uniq ( integer[] ) → integer[]

Удаляет соседние дубликаты. Часто используется вместе с sort для удаления всех дубликатов.

uniq('{1,2,2,3,1,1}'::integer[]) → {1,2,3,1}

uniq(sort('{1,2,3,2,1}'::integer[])) → {1,2,3}

idx ( integer[], item integer ) → integer

Выдаёт индекс первого элемента, равного item, или 0, если такого элемента нет.

idx(array[11,22,33,22,11], 22) → 2

subarray ( integer[], start integer, len integer ) → integer[]

Извлекает часть массива, которая начинается с позиции start и содержит len элементов.

subarray('{1,2,3,2,1}'::integer[], 2, 3) → {2,3,2}

subarray ( integer[], start integer ) → integer[]

Извлекает часть массива, которая начинается с позиции start.

subarray('{1,2,3,2,1}'::integer[], 2) → {2,3,2,1}

intset ( integer ) → integer[]

Создаёт массив с одним элементом.

intset(42) → {42}

Таблица F.15. Операторы intarray

Оператор Описание
`integer[]` `&&` `integer[]` → `boolean` Массивы пересекаются (у них есть минимум один общий элемент)?
`integer[]` `@>` `integer[]` → `boolean` Левый массив содержит правый?
`integer[]` `<@` `integer[]` → `boolean` Левый массив содержится в правом?
`#` `integer[]` → `integer` Выдаёт число элементов в массиве.
`integer[]` `#` `integer` → `integer` Выдаёт индекс первого элемента, равного правому аргументу, или 0, если такого элемента нет. (Аналог функции `idx`.)
`integer[]` `+` `integer` → `integer[]` Добавляет элемент в конец массива.
`integer[]` `+` `integer[]` → `integer[]` Соединяет два массива.
`integer[]` `-` `integer` → `integer[]` Удаляет из массива элементы, равные правому аргументу.
`integer[]` `-` `integer[]` → `integer[]` Удаляет из левого массива элементы правого массива.
`integer[]` `\|` `integer` → `integer[]` Вычисляет объединение аргументов.
`integer[]` `\|` `integer[]` → `integer[]` Вычисляет объединение аргументов.
`integer[]` `&` `integer[]` → `integer[]` Вычисляет пересечение аргументов.
`integer[]` `@@` `query_int` → `boolean` Массив удовлетворяет запросу? (см. ниже)
`query_int` `~~` `integer[]` → `boolean` Массив удовлетворяет запросу? (коммутирующий оператор к `@@`)

Оператор

Описание

integer[] && integer[] → boolean

Массивы пересекаются (у них есть минимум один общий элемент)?

integer[] @> integer[] → boolean

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

integer[] <@ integer[] → boolean

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

# integer[] → integer

Выдаёт число элементов в массиве.

integer[] # integer → integer

Выдаёт индекс первого элемента, равного правому аргументу, или 0, если такого элемента нет. (Аналог функции idx.)

integer[] + integer → integer[]

Добавляет элемент в конец массива.

integer[] + integer[] → integer[]

Соединяет два массива.

integer[] - integer → integer[]

Удаляет из массива элементы, равные правому аргументу.

integer[] - integer[] → integer[]

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

integer[] | integer → integer[]

Вычисляет объединение аргументов.

integer[] | integer[] → integer[]

Вычисляет объединение аргументов.

integer[] & integer[] → integer[]

Вычисляет пересечение аргументов.

integer[] @@ query_int → boolean

Массив удовлетворяет запросу? (см. ниже)

query_int ~~ integer[] → boolean

Массив удовлетворяет запросу? (коммутирующий оператор к @@)

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

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

F.28.2. Поддержка индексов #

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

Модуль предоставляет два параметризованных класса операторов GiST: gist__int_ops (используется по умолчанию), подходящий для маленьких и средних по размеру наборов данных, и gist__intbig_ops, применяющий сигнатуру большего размера и подходящий для индексации больших наборов данных (то есть столбцов, содержащих много различных значений массива). В этой реализации используется структура данных RD-дерева со встроенным сжатием с потерями.

Класс gist__int_ops аппроксимирует набор целых чисел в виде массива диапазонов. В его необязательном целочисленном параметре numranges можно задать максимальное число диапазонов в одном ключе индекса. Параметр может принимать значения от 1 до 253, по умолчанию он равен 100. При увеличении числа массивов, составляющих ключ индекса GiST, поиск работает точнее (сканируется меньшая область в индексе и меньше страниц кучи), но сам индекс становится больше.

Класс gist__intbig_ops аппроксимирует набор целых чисел в виде сигнатуры битовой карты. В его необязательном целочисленном параметре siglen можно задать размер сигнатуры в байтах. Параметр может принимать значения от 1 до 2024, по умолчанию он равен 16. При увеличении размера сигнатуры поиск работает точнее (сканируется меньшая область в индексе и меньше страниц кучи), но сам индекс становится больше.

Есть также нестандартный класс операторов GIN, gin__int_ops, поддерживающий эти операторы наряду с <@.

Выбор между индексами GiST и GIN зависит от относительных характеристик производительности GiST и GIN, которые здесь не рассматриваются.

F.28.3. Пример #

-- сообщение может относиться к одной или нескольким «секциям»
CREATE TABLE message (mid INT PRIMARY KEY, sections INT[], ...);

-- создать специализированный индекс с длиной сигнатуры 32 байта
CREATE INDEX message_rdtree_idx ON message USING GIST (sections gist__intbig_ops (siglen = 32));

-- вывести сообщения из секций 1 или 2 — оператор пересечения
SELECT message.mid FROM message WHERE message.sections && '{1,2}';

-- вывести сообщения из секций 1 и 2 — оператор включения
SELECT message.mid FROM message WHERE message.sections @> '{1,2}';

-- тот же результат, но с оператором запроса
SELECT message.mid FROM message WHERE message.sections @@ '1&2'::query_int;

F.28.4. Авторы #

Разработку осуществили Фёдор Сигаев (<teodor@sigaev.ru>) и Олег Бартунов (<oleg@sai.msu.su>). Дополнительные сведения можно найти на странице http://www.sai.msu.su/~megera/postgres/gist/. Андрей Октябрьский проделал отличную работу, добавив новые функции и операторы.

Пред.	Наверх	След.
F.27. intagg — агрегатор и нумератор целых чисел	Начало	F.29. isn — типы данных для международных стандартов нумерации (ISBN, EAN, UPC и т. д.)

F.28. intarray — manipulate arrays of integers
Prev	Up	Appendix F. Additional Supplied Modules and Extensions Shipped in `postgrespro-std-16-contrib`	Home	Next

F.28. intarray — manipulate arrays of integers #

F.28.1. intarray Functions and Operators
F.28.2. Index Support
F.28.3. Example
F.28.4. Authors

The intarray module provides a number of useful functions and operators for manipulating null-free arrays of integers. There is also support for indexed searches using some of the operators.

All of these operations will throw an error if a supplied array contains any NULL elements.

Many of these operations are only sensible for one-dimensional arrays. Although they will accept input arrays of more dimensions, the data is treated as though it were a linear array in storage order.

This module is considered “trusted”, that is, it can be installed by non-superusers who have CREATE privilege on the current database.

F.28.1. `intarray` Functions and Operators #

The functions provided by the intarray module are shown in Table F.14, the operators in Table F.15.

Table F.14. intarray Functions

Function Description Example(s)
`icount` ( `integer[]` ) → `integer` Returns the number of elements in the array. `icount('{1,2,3}'::integer[])` → `3`
`sort` ( `integer[]`, `dir` `text` ) → `integer[]` Sorts the array in either ascending or descending order. `dir` must be `asc` or `desc`. `sort('{1,3,2}'::integer[], 'desc')` → `{3,2,1}`
`sort` ( `integer[]` ) → `integer[]` `sort_asc` ( `integer[]` ) → `integer[]` Sorts in ascending order. `sort(array[11,77,44])` → `{11,44,77}`
`sort_desc` ( `integer[]` ) → `integer[]` Sorts in descending order. `sort_desc(array[11,77,44])` → `{77,44,11}`
`uniq` ( `integer[]` ) → `integer[]` Removes adjacent duplicates. Often used with `sort` to remove all duplicates. `uniq('{1,2,2,3,1,1}'::integer[])` → `{1,2,3,1}` `uniq(sort('{1,2,3,2,1}'::integer[]))` → `{1,2,3}`
`idx` ( `integer[]`, `item` `integer` ) → `integer` Returns index of the first array element matching `item`, or 0 if no match. `idx(array[11,22,33,22,11], 22)` → `2`
`subarray` ( `integer[]`, `start` `integer`, `len` `integer` ) → `integer[]` Extracts the portion of the array starting at position `start`, with `len` elements. `subarray('{1,2,3,2,1}'::integer[], 2, 3)` → `{2,3,2}`
`subarray` ( `integer[]`, `start` `integer` ) → `integer[]` Extracts the portion of the array starting at position `start`. `subarray('{1,2,3,2,1}'::integer[], 2)` → `{2,3,2,1}`
`intset` ( `integer` ) → `integer[]` Makes a single-element array. `intset(42)` → `{42}`

Function

Description

Example(s)

icount ( integer[] ) → integer

Returns the number of elements in the array.

icount('{1,2,3}'::integer[]) → 3

sort ( integer[], dir text ) → integer[]

Sorts the array in either ascending or descending order. dir must be asc or desc.

sort('{1,3,2}'::integer[], 'desc') → {3,2,1}

sort ( integer[] ) → integer[]

sort_asc ( integer[] ) → integer[]

Sorts in ascending order.

sort(array[11,77,44]) → {11,44,77}

sort_desc ( integer[] ) → integer[]

Sorts in descending order.

sort_desc(array[11,77,44]) → {77,44,11}

uniq ( integer[] ) → integer[]

Removes adjacent duplicates. Often used with sort to remove all duplicates.

uniq('{1,2,2,3,1,1}'::integer[]) → {1,2,3,1}

uniq(sort('{1,2,3,2,1}'::integer[])) → {1,2,3}

idx ( integer[], item integer ) → integer

Returns index of the first array element matching item, or 0 if no match.

idx(array[11,22,33,22,11], 22) → 2

subarray ( integer[], start integer, len integer ) → integer[]

Extracts the portion of the array starting at position start, with len elements.

subarray('{1,2,3,2,1}'::integer[], 2, 3) → {2,3,2}

subarray ( integer[], start integer ) → integer[]

Extracts the portion of the array starting at position start.

subarray('{1,2,3,2,1}'::integer[], 2) → {2,3,2,1}

intset ( integer ) → integer[]

Makes a single-element array.

intset(42) → {42}

Table F.15. intarray Operators

Operator Description
`integer[]` `&&` `integer[]` → `boolean` Do arrays overlap (have at least one element in common)?
`integer[]` `@>` `integer[]` → `boolean` Does left array contain right array?
`integer[]` `<@` `integer[]` → `boolean` Is left array contained in right array?
`#` `integer[]` → `integer` Returns the number of elements in the array.
`integer[]` `#` `integer` → `integer` Returns index of the first array element matching the right argument, or 0 if no match. (Same as `idx` function.)
`integer[]` `+` `integer` → `integer[]` Adds element to end of array.
`integer[]` `+` `integer[]` → `integer[]` Concatenates the arrays.
`integer[]` `-` `integer` → `integer[]` Removes entries matching the right argument from the array.
`integer[]` `-` `integer[]` → `integer[]` Removes elements of the right array from the left array.
`integer[]` `\|` `integer` → `integer[]` Computes the union of the arguments.
`integer[]` `\|` `integer[]` → `integer[]` Computes the union of the arguments.
`integer[]` `&` `integer[]` → `integer[]` Computes the intersection of the arguments.
`integer[]` `@@` `query_int` → `boolean` Does array satisfy query? (see below)
`query_int` `~~` `integer[]` → `boolean` Does array satisfy query? (commutator of `@@`)

Operator

Description

integer[] && integer[] → boolean

Do arrays overlap (have at least one element in common)?

integer[] @> integer[] → boolean

Does left array contain right array?

integer[] <@ integer[] → boolean

Is left array contained in right array?

# integer[] → integer

Returns the number of elements in the array.

integer[] # integer → integer

Returns index of the first array element matching the right argument, or 0 if no match. (Same as idx function.)

integer[] + integer → integer[]

Adds element to end of array.

integer[] + integer[] → integer[]

Concatenates the arrays.

integer[] - integer → integer[]

Removes entries matching the right argument from the array.

integer[] - integer[] → integer[]

Removes elements of the right array from the left array.

integer[] | integer → integer[]

Computes the union of the arguments.

integer[] | integer[] → integer[]

Computes the union of the arguments.

integer[] & integer[] → integer[]

Computes the intersection of the arguments.

integer[] @@ query_int → boolean

Does array satisfy query? (see below)

query_int ~~ integer[] → boolean

Does array satisfy query? (commutator of @@)

The operators &&, @> and <@ are equivalent to Postgres Pro's built-in operators of the same names, except that they work only on integer arrays that do not contain nulls, while the built-in operators work for any array type. This restriction makes them faster than the built-in operators in many cases.

The @@ and ~~ operators test whether an array satisfies a query, which is expressed as a value of a specialized data type query_int. A query consists of integer values that are checked against the elements of the array, possibly combined using the operators & (AND), | (OR), and ! (NOT). Parentheses can be used as needed. For example, the query 1&(2|3) matches arrays that contain 1 and also contain either 2 or 3.

F.28.2. Index Support #

intarray provides index support for the &&, @>, and @@ operators, as well as regular array equality.

Two parameterized GiST index operator classes are provided: gist__int_ops (used by default) is suitable for small- to medium-size data sets, while gist__intbig_ops uses a larger signature and is more suitable for indexing large data sets (i.e., columns containing a large number of distinct array values). The implementation uses an RD-tree data structure with built-in lossy compression.

gist__int_ops approximates an integer set as an array of integer ranges. Its optional integer parameter numranges determines the maximum number of ranges in one index key. The default value of numranges is 100. Valid values are between 1 and 253. Using larger arrays as GiST index keys leads to a more precise search (scanning a smaller fraction of the index and fewer heap pages), at the cost of a larger index.

gist__intbig_ops approximates an integer set as a bitmap signature. Its optional integer parameter siglen determines the signature length in bytes. The default signature length is 16 bytes. Valid values of signature length are between 1 and 2024 bytes. Longer signatures lead to a more precise search (scanning a smaller fraction of the index and fewer heap pages), at the cost of a larger index.

There is also a non-default GIN operator class gin__int_ops, which supports these operators as well as <@.

The choice between GiST and GIN indexing depends on the relative performance characteristics of GiST and GIN, which are discussed elsewhere.

F.28.3. Example #

-- a message can be in one or more “sections”
CREATE TABLE message (mid INT PRIMARY KEY, sections INT[], ...);

-- create specialized index with signature length of 32 bytes
CREATE INDEX message_rdtree_idx ON message USING GIST (sections gist__intbig_ops (siglen = 32));

-- select messages in section 1 OR 2 - OVERLAP operator
SELECT message.mid FROM message WHERE message.sections && '{1,2}';

-- select messages in sections 1 AND 2 - CONTAINS operator
SELECT message.mid FROM message WHERE message.sections @> '{1,2}';

-- the same, using QUERY operator
SELECT message.mid FROM message WHERE message.sections @@ '1&2'::query_int;

F.28.4. Authors #

All work was done by Teodor Sigaev (<teodor@sigaev.ru>) and Oleg Bartunov (<oleg@sai.msu.su>). See http://www.sai.msu.su/~megera/postgres/gist/ for additional information. Andrey Oktyabrski did a great work on adding new functions and operations.

Prev	Up	Next
F.27. intagg — integer aggregator and enumerator	Home	F.29. isn — data types for international standard numbers (ISBN, EAN, UPC, etc.)