PostgreSQL: как вставить данные в таблицу командой INSERT INTO
Команда INSERT INTO в PostgreSQL позволяет эффективно добавлять новые данные в существующие таблицы базы данных. Однако у многих разработчиков возникают сложности с ее использованием. В этой статье мы подробно разберем синтаксис, особенности работы и передовые практики применения INSERT INTO в PostgreSQL.
Основы синтаксиса INSERT INTO
INSERT INTO - это одна из наиболее часто используемых SQL-команд в PostgreSQL. Ее основное назначение - добавление новых строк в указанную таблицу базы данных.
Базовая структура выглядит следующим образом:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Здесь table_name - имя целевой таблицы, в которую будут добавлены строки, column - список столбцов, куда нужно вставить значения, VALUES - непосредственно вставляемые значения.
Порядок столбцов и значений должен строго совпадать при использовании INSERT INTO.
Например, чтобы добавить данные в таблицу links:
INSERT INTO links (url, name, description) VALUES ('https://www.postgresqltutorial.com', 'PostgreSQL Tutorial', 'PostgreSQL tutorials and guides');
Если не указывать явно список столбцов, PostgreSQL будет использовать все столбцы таблицы в порядке их объявления:
INSERT INTO links VALUES ('https://www.postgresqltutorial.com', 'PostgreSQL Tutorial', 'PostgreSQL tutorials and guides');
Вставка текстовых и дата значений
Для вставки текстовых значений используются одинарные кавычки. Например, чтобы добавить описание сайта:
INSERT INTO links (url, description) VALUES('https://www.postgresqltutorial.com', 'PostgreSQL tutorials');
Даты и время записываются в формате 'ГГГГ-ММ-ДД':
INSERT INTO logs (dt, message) VALUES ('2023-01-28', 'Successfully inserted new data');
Использование значений по умолчанию
Если в команде INSERT INTO не указать значение для какого-либо столбца, PostgreSQL автоматически подставит значение по умолчанию:
- Для числовых типов данных - 0
- Для текстовых типов данных - пустая строка
- Для булевых (boolean) - false
Также значения по умолчанию могут задаваться в определении столбцов таблицы при ее создании.
Расширенные возможности INSERT INTO
Помимо базового синтаксиса, в команде INSERT INTO есть расширенные опции, повышающие ее гибкость.
За один запрос можно добавить сразу несколько строк, указав значения через запятую:
INSERT INTO links (url, name) VALUES ('https://site1.com','Site 1'), ('https://site2.com','Site 2'), ('https://site3.com','Site 3');
Вставка данных из другой таблицы
INSERT INTO позволяет выбрать значения для вставки из другой таблицы с помощью SELECT:
INSERT INTO public.clients (name, address) SELECT last_name, location FROM contractors;
Это удобно при переносе данных или слиянии таблиц.
Предложение RETURNING
Добавив RETURNING в INSERT INTO, можно получить обратно только что вставленную строку:
INSERT INTO links (url, name) VALUES('https://site4.com','Site 4') RETURNING id, url;
Это позволяет, например, узнать значение автоинкрементного идентификатора (Sequence) новой строки.
Реализация конструкции "upsert"
PostgreSQL поддерживает реализацию популярной конструкции "upsert" (update + insert) с помощью предложения ON CONFLICT в команде INSERT. Это позволяет в одном запросе либо добавить новую строку, либо обновить существующую.
INSERT INTO table_name (column_list) VALUES (value_list) ON CONFLICT (constraint) DO UPDATE SET column1 = value1, ... WHERE condition;
Здесь при конфликте по указанному ограничению constraint (чаще всего уникальный индекс) будет выполнено обновление заданных столбцов для этой строки.
Примеры типовых задач
Рассмотрим несколько примеров использования INSERT INTO для решения распространенных задач:
1. Вставка данных с пропущенными значениями
INSERT INTO clients (first_name, last_name) VALUES ('John', NULL);
В столбец last_name будет добавлено значение NULL.
2. Атомарная вставка или обновление данных
INSERT INTO users (id, name, email) VALUES (123, 'John Dow', 'john@mail.com') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email;
Здесь при совпадении идентификатора id произойдет обновление имени и email вместо ошибки.
Обработка ошибок вставки данных
При использовании INSERT INTO возможно возникновение следующих типов ошибок:
- Нарушение ограничений целостности (NOT NULL, CHECK и др.)
- Нарушение уникальности значения (дубликат)
- Нехватка прав доступа у пользователя на вставку
- Некорректные или несовместимые значения данных
Для обработки ошибок рекомендуется использовать конструкцию EXCEPTION, позволяющую отлавливать исключения базы данных.
Рекомендации по оптимизации производительности INSERT
Для повышения скорости выполнения команд INSERT INTO рекомендуется:
- Использовать пакетные вставки данных, а не поочередные
- Выполнять вставку вне транзакции
- Отключать внешние ключи и триггеры на время массовой вставки данных
- Выполнять вставку параллельно по нескольким сессиям
Также имеет смысл оптимизировать схему таблицы с учетом INSERT-интенсивных нагрузок.