Массив PostgreSQL: функции, тип, пример

Что такое массив PostgreSQL?

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

Каждый соответствующий тип данных PostgreSQL имеет соответствующий тип массива. Например, целочисленный тип данных имеет тип массива целое[], символьный тип данных имеет тип массива символ[] и т. д.

Создание массивов PostgreSQL

В следующемwing Например, мы создадим таблицу с именем «Сотрудники» со столбцом контактов, определенным как текстовый массив:

CREATE TABLE Employees (
   id int PRIMARY KEY,
   name VARCHAR (100),
   contact TEXT []
);

Команда должна выполниться успешно.

Создание массивов PostgreSQL

Вставка значений массива PostgreSQL

Давайте сейчас вставить значения в приведенную выше таблицу:

INSERT INTO Employees 
VALUES
   (
      1,
      'Alice John',
      ARRAY [ '(408)-743-9045',
      '(408)-567-7834' ]
   );

Вставка должна пройти успешно.

Вставка значений массива PostgreSQL

Значения третьего столбца, то есть контакта, вставлены в виде массива. Это было достигнуто с помощью конструктора ARRAY.

В этом примере мы заключили их в квадратные скобки []. У нас есть два контакта сотрудницы Элис Джон.

Мы по-прежнему можем использовать фигурные скобки {}, как показано ниже:

INSERT INTO Employees 
VALUES
   (
    2,
      'Kate Joel',
      '{"(408)-783-5731"}'
   ),
   ( 
      3,
      'James Bush',
      '{"(408)-745-8965","(408)-567-78234"}'
   );

Команда должна выполниться успешно.

Вставка значений массива PostgreSQL

Приведенные выше инструкции вставят две строки в таблицу «Сотрудники». При использовании фигурных скобок массив заключается в одинарные кавычки ('), а элементы текстового массива - в двойные кавычки (").

Запрос данных массива

Чтобы запросить элементы массива, мы используем оператор SELECT.

Чтобы увидеть содержимое нашей таблицы «Сотрудники», мы запускаем следующую команду:wing команда:

SELECT * FROM Employees;

Это возвращает следующееwing:

Запрос данных массива

Элементы столбца массива, то есть контакт, заключены в фигурные скобки {}.

Чтобы получить доступ к самим элементам массива, мы добавляем индекс в квадратных скобках []. Первый элемент массива находится в позиции 1.

Например, нам нужно получить имена сотрудников и только их первый контакт для тех сотрудников, у которых более одного контакта. Мы можем получить к этому доступ как контакт[1].

Давайте посмотрим на это:

SELECT name, contact[1]
FROM Employees;

Это вернет следующееwing:

Запрос данных массива

Мы можем использовать оператор SELECT вместе с предложением WHERE для фильтрации строк на основе столбца массива.

Например, чтобы увидеть сотрудника с номером (408)-567-78234 в качестве второго контакта, мы можем запустить следующую команду:wing команда:

SELECT
   name
FROM
   Employees
WHERE
   contact [ 2 ] = '(408)-567-78234';

Это вернет следующееwing:

Запрос данных массива

Изменение массива PostgreSQL

Вы можете обновить весь или один элемент массива.

Вот содержимое таблицы «Сотрудники»:

Изменение массива PostgreSQL

Обновим второй номер телефона сотрудника Джеймса Буша, id которого равен 3:

Изменение массива PostgreSQL

Запустите следующееwing команда:

UPDATE Employees
SET contact [ 2 ] = '(408)-589-89347'
WHERE
   id = 3;

Команда должна выполниться успешно:

Изменение массива PostgreSQL

Давайте запросим таблицу, чтобы проверить, было ли изменение успешным:

Изменение массива PostgreSQL

Смена прошла успешно.

Поиск в массиве PostgreSQL

На данный момент наша таблица «Сотрудники» выглядит следующим образом:

Поиск в массиве PostgreSQL

Предположим, нам нужно знать, кому принадлежит контакт (408)-783-5731, независимо от позиции в массиве контактов. Мы можем использовать функцию ANY(), как показано ниже:

SELECT
   name,
   contact
FROM
   Employees
WHERE
   '(408)-783-5731' = ANY (contact);

Это вернет следующееwing:

Поиск в массиве PostgreSQL

Расширение массивов

Мы можем разбить значения массива на строки. Этот процесс известен как расширение массива.

В примере таблицы «Сотрудники» есть сотрудники с двумя контактами в массиве контактов. Мы можем разделить их на отдельные строки.

PostgreSQL предоставляет для этого функцию unnest().

Например:

SELECT
   name,
   unnest(contact)
FROM
   Employees;

Это вернет следующееwing:

Расширение массивов

У сотрудников Элис Джон и Джеймса Буша есть два контакта. Мы можем разделить на отдельные строки.

Использование pgAdmin

Создание массивов PostgreSQL

Чтобы сделать то же самое через pgAdmin, сделайте следующее:

Шаг 1) Войдите в свою учетную запись pgAdmin.

Шаг 2)

  1. На панели навигации слева щелкните rbases.
  2. Нажмите кнопку «Демо»

Создание массивов PostgreSQL с помощью pgAdmin

Шаг 3) Введите запрос в редакторе запросов, чтобы создать таблицу «Сотрудники»:

CREATE TABLE Employees (
   id int PRIMARY KEY,
   name VARCHAR (100),
   contact TEXT []
);

Шаг 4) Нажмите кнопку «Выполнить».

Создание массивов PostgreSQL с помощью pgAdmin

Вставка значений массива PostgreSQL

Шаг 1) Введите следующееwing запрос в редакторе запросов:

INSERT INTO Employees 
VALUES
   (
      1,
      'Alice John',
      ARRAY [ '(408)-743-9045',
      '(408)-567-7834' ]
   );

Шаг 2) Нажмите кнопку «Выполнить»:

Вставка значений массива PostgreSQL

Шаг 3)

Использование фигурных скобок в запросе

Шаг 1) Введите следующееwing запрос в редакторе запросов:

INSERT INTO Employees 
VALUES
   (
    2,
      'Kate Joel',
      '{"(408)-783-5731"}'
   ),
   ( 
      3,
      'James Bush',
      '{"(408)-745-8965","(408)-567-78234"}'
   );

Шаг 2) Нажмите кнопку «Выполнить»:

Вставка значений массива PostgreSQL

Запрос данных массива

Шаг 1) Чтобы просмотреть содержимое таблицы «Сотрудники», введите следующую команду:wing запрос в редакторе запросов:

SELECT * FROM Employees;

Шаг 2) Нажмите кнопку «Выполнить»:

Запрос данных массива с помощью pgAdmin

Он должен вернуть следующееwing:

Запрос данных массива с помощью pgAdmin

Шаг 3) Чтобы увидеть первые контакты сотрудников:

  1. Введите следующееwing запрос в редакторе запросов:
    SELECT name, contact[1]
    FROM Employees;
    
  2. Нажмите кнопку «Выполнить».

Запрос данных массива с помощью pgAdmin

Он должен вернуть следующееwing:

Запрос данных массива с помощью pgAdmin

Шаг 4) Чтобы объединить оператор SELECT с предложением WHERE:

  1. Введите следующееwing команда в редакторе запросов:
    SELECT
       name
    FROM
       Employees
    WHERE
       contact [ 2 ] = '(408)-567-78234';
    
  2. Нажмите кнопку «Выполнить».

Запрос данных массива с помощью pgAdmin

Он должен вернуть следующееwing:

Запрос данных массива с помощью pgAdmin

Изменение массива PostgreSQL

Шаг 1) Чтобы обновить второй контакт пользователя с идентификатором 3, запустите следующую команду:wing команда:

UPDATE Employees
SET contact [ 2 ] = '(408)-589-89347'
WHERE
   id = 3;

Шаг 2) Нажмите кнопку «Выполнить».

Изменение массива PostgreSQL с помощью pgAdmin

Шаг 3)

1. Введите следующееwing команда в редакторе запросов, чтобы проверить, было ли изменение успешным:

SELECT * FROM Employees;

2.Нажмите кнопку «Выполнить».

Изменение массива PostgreSQL с помощью pgAdmin

Он должен вернуть следующееwing:

Изменение массива PostgreSQL с помощью pgAdmin

Поиск в массиве PostgreSQL

Шаг 1) Введите следующееwing запрос в редакторе запросов:

SELECT
   name,
   contact
FROM
   Employees
WHERE
   '(408)-783-5731' = ANY (contact);

Шаг 2) Нажмите кнопку «Выполнить».

Поиск в массиве PostgreSQL с помощью pgAdmin

Он должен вернуть следующееwing:

Поиск в массиве PostgreSQL с помощью pgAdmin

Расширение массивов

Шаг 1) Введите следующееwing запрос в редакторе запросов:

SELECT
   name,
   unnest(contact)
FROM
   Employees;

Шаг 2) Нажмите кнопку «Выполнить».

Расширение массивов с помощью pgAdmin

Он должен вернуть следующееwing:

Расширение массивов с помощью pgAdmin

Итоги

  • PostgreSQL позволяет нам определить столбец таблицы как тип массива.
  • Массив должен быть допустимым тип данных такие как целочисленные, символьные или определяемые пользователем типы.
  • Чтобы вставить значения в столбец массива, мы используем конструктор ARRAY.
  • Если в одной строке столбца массива находится более одного элемента, первый элемент находится в позиции 1.
  • Доступ к каждому значению можно получить, передав индекс в квадратных скобках [].
  • Элементы массива можно получить с помощью оператора SELECT.
  • Значения столбца массива могут быть заключены в квадратные скобки [] или фигурные скобки {}.
  • Мы можем искать значения столбцов массива, используя функцию ANY().

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