Учебники

9) Массив PostgreSQL

Август 1, 2019

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

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

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

В этом уроке по PostgreSQL вы узнаете:

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

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

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

Команда должна успешно работать.

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

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

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

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

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

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

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

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

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

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

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

Для запроса элементов массива мы используем оператор SELECT.

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

SELECT * FROM Employees;

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

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

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

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

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

SELECT name, contact[1]
FROM Employees;

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

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

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

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

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

Модификация массива PostgreSQL

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

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

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

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

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

Команда должна успешно работать:

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

Изменение было успешным.

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

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

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

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

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

Расширяющиеся массивы

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

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

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

Например: 

SELECT
   name,
   unnest(contact)
FROM
   Employees;

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

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

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

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

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

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

Шаг 2)

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

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

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

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

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

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

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

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

Шаг 3)

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

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

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

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

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

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

SELECT * FROM Employees;

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

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

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

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

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

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

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

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

Модификация массива PostgreSQL

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

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

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

Шаг 3)

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

SELECT * FROM Employees;

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

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

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

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

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

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

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

Расширяющиеся массивы

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

SELECT
   name,
   unnest(contact)
FROM
   Employees;

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

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

Резюме:

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

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

 

207
Share: