PostgreSQL | Как вставить данные в ячейки одного столбца таблицы из строкового типа по разделителю используя один INSERT?

PostgreSQL | Как вставить данные в ячейки одного столбца таблицы из строкового типа по разделителю используя один INSERT?

Как коротко выполнить вставку 1000 записей в один столбец из одной строки данных в PostgreSQL?

Смысл задачи состоит в том, что у нас есть какая-то строка с данными. Строка — подразумевается строковый тип (как String в JavaScript) данных в PostgreSQL.

В этой строке данные разделены при помощи двух символов «, » (ЗАПЯТАЯ и ПРОБЕЛ).

Мы хотим разделить строку на несколько элементов отдельных данных, которые упадут вертикально в столбец определённой таблицы.

Как это сделать?

 

Решение в PostgreSQL

Эта задача состоит из нескольких этапов. Мы их будем разбирать шаг за шагом.

Давайте для начала посмотрим на строку:

'2017, 2018, 2019, 2020, 2021, 2022'

Строки в PostgreSQL оформляются при помощи одинарных кавычек. В этой строке данных перечислены года, разделённые через РАЗДЕЛИТЕЛЬ. Эта стока пришла из другой системы и нам нужно с ней правильно поработать при помощи инструментов PostgreSQL. Это строковый тип данных, а значит после разделения мы также получим года в строковых типах, а не в числовых.

Строка с годами в PostgreSQL
Строка с годами в PostgreSQL

В таком виде строку нельзя использовать в качестве команды т. к. среда выполнения кода SQL выдаст ошибку синтаксиса. Я привожу её для примера, чтобы вы отдельно посмотрели на ИСХОДНЫЕ ДАННЫЕ.

Ошибка синтаксиса в PostgreSQL - обращение к строке данных
Ошибка синтаксиса в PostgreSQL — обращение к строке данных

Вот эти года мы хотим «перевернуть вертикально» и положить в столбец какой-то таблицы.

 

Шаг № 1 — Правильное обращение к строковому типу данных через SELECT в PostgreSQL

В PostgreSQL команда SELECT обладает большим количеством возможностей. Её нужно воспринимать как «ЗАПУСК ЗАДАНИЯ в SQL». Это ключевое слово, которое является зарезервированным и которое помогает ПОЛУЧАТЬ «ЧТО-ТО» из «ЧЕГО-ТО«. Без команды SELECT вообще ничего не работает. Говоря кухонным языком, воспринимайте СЕЛЕКТ как «Привет! Как дела?«.

Давайте теперь начнём наше обращение к строке при помощи SELECT:

select '2017, 2018, 2019, 2020, 2021, 2022';
Обратились к строке данных через SELECT в PostgreSQL
Обратились к строке данных через SELECT в PostgreSQL

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

Что тут важно? Тип данных. В нашем случае тип данных распознался в PostgreSQL, как text. Ну так оно и есть. А ещё название столбца приняло интересную форму — ?column?

 

Шаг № 2 — Конвертация строки в массив данных по разделителю в PostgreSQL

На этом этапе мы уже можем начать делить нашу строку с данными.

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

Под строками в данном контексте подразумеваются значения типов character, character varying и text. Если не отмечено обратное, эти функции и операторы принимают и возвращают тип text. С тем же успехом в аргументах может передаваться тип character varying. Аргументы же типа character до вызова оператора или функции приводятся к типу text, вследствие чего завершающие пробелы в значении character будут обрезаться.

Нас интересует функция преобразования строки данных в массив, которая называется string_to_array()

 

Синтаксис функции string_to_array() в PostgreSQL

string_to_array ( string text, delimiter text [, null_string text] )

При любых раскладах функция принимает два параметра. Третий опционально — либо ДА, либо НЕТ.

Первый параметр — это наша строка данных

Второй параметр — это строка, которую нужно воспринимать как разделитель.

Третий параметр — какую строку воспринимать как NULL (как пустоту).

На выходе мы получаем массив строк с типом text.

 

select string_to_array('2017, 2018, 2019, 2020, 2021, 2022', ', ');
Разделили строку с годами на массив в PostgreSQL по разделителю
Разделили строку с годами на массив в PostgreSQL по разделителю

Интерфейс pgAdmin 4, в котором я веду разработку и ввод команд отображает МАССИВ из СТРОК SQL в фигурных скобках. Это немного запутывает, потому что в нормальных языках программирования фигурными скобками помечаются ОБЪЕКТЫ, а не МАССИВЫ. При этом в заголовке столбца справа от типа данных отображаются две квадратные скобки «text[]«, одна за другой, образуя при этом вертикальный прямоугольник. Тупняк! Ну да ладно.

Важно сейчас уловить то, что наши отдельные строчки данных лежат в массиве, который сам лежит в одной записи. Под ЗАПИСЬЮ я имею ввиду одну ЛИНИЮ таблицы.

 

Шаг № 3 — Переворот массива со строками отдельных данных вертикально в PostgreSQL. Одну запись превращаем в несколько

Теперь нужно просто разложить элементы массива на отдельные записи таблицы.

В игру вступают «Функции и операторы для работы с массивами».

В этом нам поможет функция unnest().

Разворачивает массив в набор ЗАПИСЕЙ таблицы. Элементы массива прочитываются в порядке хранения.

select unnest(string_to_array('2017, 2018, 2019, 2020, 2021, 2022', ', '));

На выходе получаем табличку из нескольких строк и одного столбца:

Перевернули массив из строк SQL вертикально при помощи функции unnest - PostgreSQL
Перевернули массив из строк SQL вертикально при помощи функции unnest — PostgreSQL

Часть задач выполнена. Обратите внимание на заголовок столбца, он называется «unnest». Это как напоминалочка, что данные пришли из функции.

 

Шаг № 4 — Таблица для вставки записей из расчленённой строки в PostgreSQL

Давайте у нас будет простейшая таблица, куда мы будем заливать записи. Схема называется test1. Таблица называется t101.

CREATE TABLE IF NOT EXISTS test1.t101
(
  id integer NOT NULL DEFAULT nextval('test1.t101_id_seq'::regclass),
  "colA" text COLLATE pg_catalog."default",
  "colB" text COLLATE pg_catalog."default",
  CONSTRAINT t101_pkey PRIMARY KEY (id)
);

Выглядит она так:

Простая таблица с тремя столбцами в PostgreSQL
Простая таблица с тремя столбцами в PostgreSQL

В ней столбец id является первичным ключом с автоинкрементом. Столбцы colA и colB просто ждут текстовые значения (данные). Таблица пустая и может хранить NULL-ы.

 

Шаг № 5 — Вставка нескольких строк данных в один вызов команды INSERT в PostgreSQL

Теперь мы можем залить наши строки в один столбец готовой таблицы:

insert into test1.t101("colB") select unnest(string_to_array('2017, 2018, 2019, 2020, 2021, 2022', ', '));

Мы оборачиваем двойными кавычками имя столбца colB, потому что в этом имени есть заглавная буква. PostgreSQL регистро-зависимый и есть разница в каком регистре были созданы названия столбцов, таблиц и тому подобного. На будущее можете просто называть всё в нижнем регистре, чтобы не бояться потерять двойные кавычки.

И теперь проверяем как заполнился столбец в нашей таблице.

select * from test1.t101;
Один INSERT один SELECT много вставок много записей в PostgreSQL
Один INSERT один SELECT много вставок много записей в PostgreSQL

Года раскидались по нескольким ЗАПИСЯМ и ЯЧЕЙКАМ таблицы как мы этого и хотели — из обычной строки данных.

 

Итог

Мы научились загружать записи в столбец таблицы, используя одну команду INSERT и одну команду SELECT.

 

Информационные ссылки

Официальный сайт PostgreSQLhttps://www.postgresql.org

Графический веб-интерфейс pgAdmin для управления PostgreSQL в браузере — https://www.pgadmin.org

 

Раздел «Строковые функции и операторы» — https://postgrespro.ru/docs/postgresql/14/functions-string

Функция string_to_array()

Раздел «Функции и операторы для работы с массивами» — https://postgrespro.ru/docs/postgresql/14/functions-array

Функция unnest()

Команда INSERThttps://postgrespro.ru/docs/postgresql/14/sql-insert

 

Ключевые слова

Строку в столбец

Строку в столбец по разделителю

postgresql создать таблицу из одной строки по разделителю