INSERT: Добавление новых данных

Оператор PostgreSQL INSERT: Добавление данных в ваши таблицы

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

Введение в оператор INSERT

Оператор INSERT в PostgreSQL используется для добавления новых строк в таблицу. Вы можете вставить данные во все столбцы или только в определённые, вставить несколько строк сразу, а также импортировать данные из других таблиц.

Базовый синтаксис

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
  • INSERT INTO: Определяет таблицу, в которую будут вставляться данные.
  • VALUES: Перечисляет значения, вставляемые в столбцы.

Вставка данных в таблицы PostgreSQL

Давайте исследуем различные способы использования оператора INSERT с практическими примерами на примере нашей знакомой библиотеки и базы данных с фильмами.

1. Вставка одной строки

Пример: Добавить новую книгу в таблицу books

INSERT INTO books (title, author, price, pages)
VALUES ('Brave New World', 'Aldous Huxley', 9.99, 268);

Просмотр инициализации схемы

Объяснение:

  • В таблицу books добавляется новая книга под названием 'Brave New World' с указанным автором, ценой и количеством страниц.

2. Вставка нескольких строк

Вы можете вставлять несколько строк в одном операторе, разделяя наборы значений запятыми.

Пример: Добавить нескольких авторов в таблицу authors

INSERT INTO authors (name)
VALUES ('Aldous Huxley'),
       ('Ray Bradbury'),
       ('Kurt Vonnegut');

Объяснение:

  • В таблицу authors добавлены три новых автора.

3. Вставка данных в определённые столбцы

Если у вас нет данных для всех столбцов, вы можете вставить данные только в определённые столбцы. Неуказанные столбцы получат свои значения по умолчанию или NULL, если значение по умолчанию не установлено.

Пример: Вставить фильм без указания дополнительных данных

Так как в нашей таблице movies есть только столбец title, мы можем вставить данные только в этот столбец.

INSERT INTO movies (title)
VALUES ('Blade Runner');

Объяснение:

  • В таблицу movies добавляется новый фильм под названием 'Blade Runner'.

4. Использование значений по умолчанию

Вы можете использовать ключевое слово DEFAULT, чтобы явно указать значение по умолчанию для столбца, если оно определено для вашей таблицы.

Пример: Вставить книгу с ценой по умолчанию

Сначала изменим таблицу books, чтобы она имела значение по умолчанию для цены (если это ещё не сделано):

-- Изменить таблицу books, чтобы столбец price имел значение по умолчанию
ALTER TABLE books ALTER COLUMN price SET DEFAULT 9.99;
 
-- Вставить книгу, используя цену по умолчанию
INSERT INTO books (title, author, pages)
VALUES ('Fahrenheit 451', 'Ray Bradbury', 194);

Объяснение:

  • Книга 'Fahrenheit 451' добавляется с ценой по умолчанию $9.99.

5. Вставка данных из другой таблицы

Вы можете вставлять данные в таблицу, выбирая их из другой таблицы с помощью оператора INSERT INTO ... SELECT.

Пример: Скопировать авторов в новую таблицу

-- Создать новую таблицу для классических авторов
CREATE TABLE classic_authors (name VARCHAR(255));
 
-- Вставить авторов из таблицы authors в classic_authors
INSERT INTO classic_authors (name)
SELECT name
FROM authors
WHERE name IN ('George Orwell', 'Jane Austen', 'Leo Tolstoy');

Объяснение:

  • Создана новая таблица classic_authors.
  • Авторы, соответствующие указанным именам, копируются в таблицу classic_authors.

6. Возвращение вставленных данных

Клаузула RETURNING позволяет возвращать данные из вставленных строк.

Пример: Вставить книгу и вернуть её название и цену

INSERT INTO books (title, author, price, pages)
VALUES ('Slaughterhouse-Five', 'Kurt Vonnegut', 8.99, 275)
RETURNING title, price;

Объяснение:

  • Книга вставляется, и названия и цены вставленных строк возвращаются.

7. Обработка конфликтов с помощью ON CONFLICT (Upsert)

Клаузула ON CONFLICT позволяет вам указать альтернативное действие, если вставка нарушает уникальное ограничение.

Сначала необходимо установить уникальное ограничение на соответствующий столбец.

Пример: Вставить автора или ничего не делать, если он уже существует

Сначала убедитесь, что на столбце name установлено уникальное ограничение:

ALTER TABLE authors ADD CONSTRAINT unique_name UNIQUE (name);

Теперь используем ON CONFLICT:

INSERT INTO authors (name)
VALUES ('George Orwell')
ON CONFLICT (name) DO NOTHING;

Объяснение:

  • Попытка вставить 'George Orwell' в таблицу authors.
  • Если автор 'George Orwell' уже существует, вставка пропускается.

8. Использование INSERT с подзапросами

Вы можете использовать подзапросы для генерации значений для вставки.

Пример: Вставить авторов из книг, которых еще нет в таблице authors

Предположим, у нас есть несколько книг, авторы которых еще не добавлены в таблицу authors.

Сначала найдём авторов в таблице books, которых нет в таблице authors, и вставим их в таблицу authors.

INSERT INTO authors (name)
SELECT DISTINCT author
FROM books
WHERE author NOT IN (SELECT name FROM authors);

Объяснение:

  • Авторы из таблицы books, которые еще не находятся в таблице authors, вставляются в таблицу authors.
  • В нашей схеме все авторы уже присутствуют, поэтому новые авторы не будут добавлены, если только не будут добавлены новые книги с новыми авторами в таблицу books.

9. Вставка данных JSON

Наша схема не включает таблиц с колонками типа JSON, но мы можем создать такую.

Пример: Вставить JSON-объект в таблицу

Сначала создадим таблицу с колонкой типа JSON:

CREATE TABLE book_info (
    id SERIAL PRIMARY KEY,
    info JSONB
);

Вставим данные JSON:

INSERT INTO book_info (info)
VALUES ('{"title": "Dune", "author": "Frank Herbert", "price": 10.99, "pages": 412}');

Объяснение:

  • В таблицу book_info вставляется JSON-объект, содержащий информацию о книге.

10. Копирование данных между таблицами

Вы можете копировать данные из одной таблицы в другую, включая преобразования.

Пример: Копировать книги в таблицу discounted_books с 10% скидкой

Создаём таблицу discounted_books:

CREATE TABLE discounted_books (
    title VARCHAR(255),
    author VARCHAR(255),
    discounted_price NUMERIC(10, 2)
);

Вставим данные с преобразованными значениями:

INSERT INTO discounted_books (title, author, discounted_price)
SELECT title, author, price * 0.9
FROM books;

Объяснение:

  • Все книги копируются в таблицу discounted_books с применённой 10% скидкой к цене.

Заключение

Оператор INSERT в PostgreSQL — это универсальный инструмент для добавления данных в ваши таблицы. Независимо от того, вставляете ли вы одну строку, несколько строк или данные из других таблиц, освоение оператора INSERT является ключом к эффективному управлению базой данных.

Основные выводы:

  • Используйте INSERT INTO ... VALUES для добавления новых данных.
  • Вставляйте несколько строк, предоставляя несколько наборов значений.
  • Указывайте столбцы для вставки данных в определённые столбцы.
  • Используйте RETURNING, чтобы получить информацию о вставленных строках.
  • Обрабатывайте конфликты с помощью ON CONFLICT, чтобы предотвратить ошибки.
  • Вставляйте данные из других таблиц, используя INSERT INTO ... SELECT.
  • Используйте поддержку типов данных JSON в PostgreSQL.

Schema Initialization

-- Создать таблицу books
CREATE TABLE books (
    title VARCHAR(255),
    author VARCHAR(255),
    price NUMERIC(10, 2),
    pages INTEGER
);
 
-- Вставить тестовые данные в таблицу books
INSERT INTO books (title, author, price, pages) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 180),
('To Kill a Mockingbird', 'Harper Lee', 7.99, 281),
('1984', 'George Orwell', 8.99, 328),
('Pride and Prejudice', 'Jane Austen', 6.99, 279),
('The Catcher in the Rye', 'J.D. Salinger', 9.99, 214),
('Moby-Dick', 'Herman Melville', 11.99, 635),
('War and Peace', 'Leo Tolstoy', 12.99, 1225);
 
-- Создать таблицу authors
CREATE TABLE authors (
    name VARCHAR(255)
);
 
-- Вставить тестовые данные в таблицу authors
INSERT INTO authors (name) VALUES
('F. Scott Fitzgerald'),
('Harper Lee'),
('George Orwell'),
('Jane Austen'),
('J.D. Salinger'),
('Herman Melville'),
('Leo Tolstoy');
 
-- Создать таблицу movies
CREATE TABLE movies (
    title VARCHAR(255)
);
 
-- Вставить тестовые данные в таблицу movies
INSERT INTO movies (title) VALUES
('The Shawshank Redemption'),
('The Godfather'),
('The Dark Knight'),
('Pulp Fiction'),
('The Lord of the Rings');

Применяйте этот шаблон для практических занятий по вставке данных, чтобы улучшить ваше понимание оператора INSERT в PostgreSQL. Удачного кодирования!

GROUP BY: Агрегация данныхUPDATE: Изменение данных