Использование массивов в PostgreSQL: руководство

Когда вы думаете об образах таблиц SQL, на ум, вероятно, приходят запросы SELECT и концепция одной строки на точку данных без вложенной структуры.

Но что, если вы хотите сохранить в строке список номеров, продуктов и т. д.?

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

С помощью ARRAY PostgreSQL позволяет определять столбцы таблицы как многомерные массивы переменной длины. Можно создавать массивы любого встроенного или определяемого пользователем базового типа, типа перечисления, составного типа, типа диапазона или домена.

Массивы могут принимать разные формы, и существует множество способов их объявления.

Вы можете использовать ключевое слово ARRAY после объявления типа данных для столбца, чтобы указать, что вы хотите создать массив ранее объявленного типа данных, как показано ниже.

Вы также можете следовать за объявлением типа данных квадратными скобками, например text[], но я считаю, что это менее явно. И это не соответствует стандарту SQL, что соответствует версии ключевого слова.

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

Другой способ добавить массив в таблицу — использовать синтаксис {} вместо ключевого слова ARRAY с скобками. Оба способа работают. Вам просто нужно обратить пристальное внимание на цитаты в фигурных скобках. Опять же, я считаю, что синтаксис ключевых слов более явный, а скобки ближе к представлениям массивов в других языках программирования, но оба работают нормально.

Подробнее о Data Science10 продвинутых концепций SQL, которые следует знать для прохождения собеседований по Data Science

Что, если вам действительно нужно разобрать или разложить массив для определенных строк, чтобы присоединиться к какой-либо таблице с информацией о продукте или выполнить другие операции над элементами списка?

Без проблем. Массивы в PostgreSQL можно легко отключить с помощью ключевого слова UNNEST:

Как и ожидалось, UNNEST создает по одной строке для каждого элемента соответствующего массива в исходной строке:

Вы можете задаться вопросом, нужно ли вам отключать массив каждый раз, когда вы хотите получить доступ к одному элементу для дальнейших операций. Если это так, то структура массива может быть не очень хорошей идеей. К счастью, массивы в PostgreSQL допускают доступ через индексацию с помощью скобок и срезов, как показано ниже. Это позволяет легко выбрать, например, первый элемент в каждом списке или определенный диапазон элементов в списке.

Индексация в массивах PostgreSQL начинается с единицы, а не с нуля, что может отличаться от того, к чему вы привыкли в других языках программирования.

Срезы работают с синтаксисом [start:end]. Доступ к элементу массива посредством разрезания всех строк, содержащих более двух элементов массива.

Если вы не уверены, сколько элементов содержит ваш массив, или хотите выполнить фильтрацию по размеру массива, как я сделал выше, вы можете использовать ключевое слово CARDINALITY. Это вернет количество элементов в массиве в виде целого числа.

Что, если вы хотите выбрать все корзины покупок, в массиве которых есть определенный товар? Или любая другая операция фильтра, основанная на элементе массива? Нет проблем, PostgreSQL позволяет использовать значение элемента в предложении WHERE в сочетании с ключевым словом ANY.

Ниже я фильтрую строки, содержащие «product_c» в столбце массива продуктов. Этот способ фильтрации наиболее полезен для отдельных значений фильтра.

Вы также можете расширить логику фильтра, описанную выше, и запросить любую карту покупок, содержащую определенный подмассив. Здесь я использую оператор «@>», который означает «содержит». Его можно прочитать следующим образом:

«Массив продуктов содержит массив ['product_a', 'product_b'].»

Массивы и значения массивов можно обновлять аналогично другим типам данных с помощью предложения UPDATE… SET…, как показано ниже. Вы можете обновить один элемент массива посредством индексации или весь массив.