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

Какво е PostgreSQL масив?

In PostgreSQL, можем да дефинираме колона като масив от валидни типове данни. Типът данни може да бъде вграден, дефиниран от потребителя или изброен тип. Освен това, масивите играят важна роля в PostgreSQL.

Всеки съответстващ PostgreSQL типът данни идва с подходящ тип масив. Например типът данни integer има тип масив integer[], типът данни character има тип масив character[] и т.н.

Създаване PostgreSQL Масивите

В следващия пример ще създадем таблица с име Employees с колона за контакт, дефинирана като текстов масив:

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.

За да видите съдържанието на нашата таблица Служители, изпълняваме следната команда:

SELECT * FROM Employees;

Това връща следното:

Запитване на данни от масив

Елементите на колоната на масива, тоест контактът, са оградени във фигурни скоби {}.

За достъп до самите елементи на масива добавяме долен индекс в квадратни скоби []. Първият елемент в масива е на позиция 1.

Например трябва да получим имената на служителите и само първия им контакт за тези служители с повече от един контакт. Имаме достъп до това като контакт[1].

Нека видим това:

SELECT name, contact[1]
FROM Employees;

Това ще върне следното:

Запитване на данни от масив

Можем да използваме оператора SELECT заедно с клаузата WHERE, за да филтрираме редове въз основа на колоната на масива.

Например, за да видим служителя с (408)-567-78234 като втори контакт, можем да изпълним следната команда:

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

Това ще върне следното:

Запитване на данни от масив

Промяна на PostgreSQL Array

Можете да актуализирате всички или един елемент от масив.

Ето съдържанието на таблицата Служители:

Промяна на PostgreSQL Array

Нека актуализираме втория телефонен номер на служителя Джеймс Буш, чийто идентификатор е 3:

Промяна на PostgreSQL Array

Изпълнете следната команда:

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

Командата трябва да се изпълни успешно:

Промяна на PostgreSQL Array

Нека направим запитване към таблицата, за да проверим дали промяната е била успешна:

Промяна на PostgreSQL Array

Промяната беше успешна.

Търсене в a PostgreSQL Array

В момента нашата таблица на служителите е както следва:

Търсене в a PostgreSQL Array

Да предположим, че трябва да знаем кой притежава контакта (408)-783-5731, независимо от позицията в масива с контакти, можем да използваме функцията ANY(), както е показано по-долу:

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

Това ще върне следното:

Търсене в a PostgreSQL Array

Разширяване на масиви

Можем да разделим стойностите на масив на редове. Този процес е известен като разширяване на масив.

В примера на таблицата Employees има служители с два контакта в масива с контакти. Можем да ги разделим на отделни редове.

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

Например:

SELECT
   name,
   unnest(contact)
FROM
   Employees;

Това ще върне следното:

Разширяване на масиви

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

Използване на pgAdmin

Създаване PostgreSQL Масивите

За да постигнете същото чрез pgAdmin, направете следното:

Стъпка 1) Влезте във вашия pgAdmin акаунт.

Стъпка 2)

  1. От лентата за навигация вляво щракнете върху бази.
  2. Кликнете върху бутона Демо

Създаване PostgreSQL Масиви с помощта на pgAdmin

Стъпка 3) Въведете заявката в редактора на заявки, за да създадете таблицата Служители:

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

Стъпка 4) Щракнете върху бутона Изпълнение.

Създаване PostgreSQL Масиви с помощта на pgAdmin

Поставяне PostgreSQL Стойности на масива

Стъпка 1) Въведете следната заявка в редактора на заявки:

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

Стъпка 2) Щракнете върху бутона Изпълнение:

Поставяне PostgreSQL Стойности на масива

Стъпка 3)

За да използвате фигурни скоби в заявката

Стъпка 1) Въведете следната заявка в редактора на заявки:

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

Стъпка 2) Щракнете върху бутона Изпълнение:

Поставяне PostgreSQL Стойности на масива

Запитване на данни от масив

Стъпка 1) За да видите съдържанието на таблицата Служители, въведете следната заявка в редактора на заявки:

SELECT * FROM Employees;

Стъпка 2) Щракнете върху бутона Изпълнение:

Извършване на заявки за масивни данни с помощта на pgAdmin

Трябва да върне следното:

Извършване на заявки за масивни данни с помощта на pgAdmin

Стъпка 3) За да видите първите контакти на служителите:

  1. Въведете следната заявка в редактора на заявки:
    SELECT name, contact[1]
    FROM Employees;
    
  2. Щракнете върху бутона Изпълнение.

Извършване на заявки за масивни данни с помощта на pgAdmin

Трябва да върне следното:

Извършване на заявки за масивни данни с помощта на pgAdmin

Стъпка 4) За да комбинирате израза SELECT с клаузата WHERE:

  1. Въведете следната команда в редактора на заявки:
    SELECT
       name
    FROM
       Employees
    WHERE
       contact [ 2 ] = '(408)-567-78234';
    
  2. Щракнете върху бутона Изпълнение.

Извършване на заявки за масивни данни с помощта на pgAdmin

Трябва да върне следното:

Извършване на заявки за масивни данни с помощта на pgAdmin

Промяна на PostgreSQL Array

Стъпка 1) За да актуализирате втория контакт на потребител с id 3, изпълнете следната команда:

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

Стъпка 2) Щракнете върху бутона Изпълнение.

Промяна на PostgreSQL Масив с помощта на pgAdmin

Стъпка 3)

1. Въведете следната команда в редактора на заявки, за да проверите дали промяната е успешна:

SELECT * FROM Employees;

2. Щракнете върху бутона Изпълнение.

Промяна на PostgreSQL Масив с помощта на pgAdmin

Трябва да върне следното:

Промяна на PostgreSQL Масив с помощта на pgAdmin

Търсене в a PostgreSQL Array

Стъпка 1) Въведете следната заявка в редактора на заявки:

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

Стъпка 2) Щракнете върху бутона Изпълнение.

Търсене в a PostgreSQL Масив с помощта на pgAdmin

Трябва да върне следното:

Търсене в a PostgreSQL Масив с помощта на pgAdmin

Разширяване на масиви

Стъпка 1) Въведете следната заявка в редактора на заявки:

SELECT
   name,
   unnest(contact)
FROM
   Employees;

Стъпка 2) Щракнете върху бутона Изпълнение.

Разширяване на масиви с помощта на pgAdmin

Трябва да върне следното:

Разширяване на масиви с помощта на pgAdmin

Oбобщение

  • PostgreSQL ни позволява да дефинираме колона на таблица като тип масив.
  • Масивът трябва да е валиден тип данни като цели числа, знаци или дефинирани от потребителя типове.
  • За да вмъкнем стойности в колона от масив, използваме конструктора ARRAY.
  • Ако има повече от един елемент в един и същ ред на колона от масив, първият елемент е на позиция 1.
  • Всяка стойност може да бъде достъпна чрез подаване на долен индекс в квадратни скоби [].
  • Елементите на масива могат да бъдат извлечени с помощта на оператора SELECT.
  • Стойностите на колоната на масива могат да бъдат оградени в квадратни скоби [] или къдрави скоби {}.
  • Можем да търсим стойности на колони от масиви с помощта на функцията ANY().

Изтеглете базата данни, използвана в този урок