SQL-Ex blog

В тестовой базе данных, которую я использую при работе с этой серией статей, я создал пару схем и пару таблиц в них. Если вы хотите выполнять код или посмотреть структуры данных, вы найдете код с моем репозитории ScaryDBA/LearningPostgreSQL. Объекты и база данных, которые вам потребуются, можно создать/восстановить с помощью скрипта CreateDatabase.sql, после чего добавьте тестовые данные, используя скрипт SampleData.sql. Остальной код из этой статьи находится в папке 09_DataManipulation.

INSERT

Мы можем также начать с добавления данных в таблицу. В целом поведение INSERT во многом ожидаемо для тех, кто имеет опыт работы с SQL Server:

INSERT

	INTO

	radio.radios

(radio_name,

	manufacturer_id,

	connectortype_id,

	digitalmode_id)

VALUES

('GD-88',

7,

3,

2);

Базовое поведение довольно простое. Вы определяете оператор: INSERT INTO (Заметьте, что INTO не является необязательным в PostgreSQL.). Затем вы сообщаете, к какой таблице адресуется запрос, включая схему: radio.radios. Да, подобно SQL Server, вы можете опустить схему, и движок PostgreSQL решит это за вас. Не делайте так. Хорошей практикой является указание таблиц, включая схему. Вы избежите проблем в будущем, усвоив эту привычку.

Затем вы перечисляете столбцы. Подобно работе с кодом SQL Server (и большинства других РСУБД), вы можете опустить столбцы, которые имеют значения по умолчанию (или допускают NULL-значения). В нашем случае имеется столбец radio_id, который является последовательностью, поэтому я не указываю его в списке столбцов. Предложение VALUES - это то место, где вы перечисляете значения, которые должны быть установлены для столбцов при вставке строки.

Если вы хотите добавить несколько строк сразу, есть очень простой синтаксис, как и в SQL Server.

INSERT

INTO

radio.radios

(radio_name,

manufacturer_id,

connectortype_id,

digitalmode_id)

VALUES

('FT300DR',

1,

4,

1),

--добавляем вторую строку

('IC-V86',

2,

2,

NULL);

Я просто добавил запятую, разделяющую множество скобок в VALUES. Также можно увидеть, как я поступаю с значением NULL - просто использую определенное ключевое слово.

Как и в случае SQL Server, вы можете построить оператор INSERT с источником строк на основе SELECT:

INSERT

INTO

radio.radiobands 

(radio_id,

band_id)

SELECT

9,

band_id

FROM

radio.radiobands

WHERE

radio_id = 2;

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

Наконец, вы можете использовать значение по умолчанию, которое определено для столбца (или столбца, который допускает NULL), с использованем ключевого слова DEFAULT:

INSERT

INTO

radio.connectortypes (connectorytype_id,

connectortype_name)

VALUES

(DEFAULT,

'F-Type Male');

В этом случае, поскольку connectortype_id определяется свойством identity, мы автоматически получаем значение, поэтому использование DEFAULT позволяет нам по-прежнему перечислить столбец, но мы не должны предоставлять значение (в данном случае мы и не сможем).

При наличии значений по умолчанию для всех столбцов вы можете написать такой оператор INSERT:

INSERT

INTO radio.connectorytypes

DEFAULT VALUES;

Однако для этой базы данных будет получена ошибка, поскольку нет значения по умолчанию для столбца connectorytype_name.

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

Одно небольшое отличие состоит в том, что ключевое слово INTO должно быть частью синтаксиса. В то время как вы можете опустить его в SQL Server. Что касается меня, то я стараюсь использовать его, потому что именно так я научился использовать T-SQL, и я думаю, что INTO добавляет ясности. PostgreSQL заставляет его использовать, поскольку это часть стандарта ANSI, которому более точно следует PostgreSQL по сравнению с большинством других систем управления базами данных.

Другой полезной функцией, которой нет в SQL Server, является возможность использовать OVERRIDING для добавления ваших собственных данных, а не генерируемых системой.

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

INSERT INTO

radio.bands

(band_id,

band_name,

frequency_start_khz,

frequency_end_khz,

country_id)

 

OVERRIDING SYSTEM VALUE

 

VALUES (10,

'6 Meters',

50000,

54000,

1);

Предложение OVERRIDING SYSTEM VALUE позволяет мне добавить собственное значение в столбец band_id, игнорируя свойство identity для этого столбца. Это нельзя сделать в операторе INSERT в T-SQL без необходимости изменить настройки прежде, чем выполнить оператор INSERT. Ясно, что так намного проще.

Наконец, действительно ловкий трюк, который нельзя выполнить в SQL Server, ON CONFLICT:

INSERT

INTO

radio.antenna 

(antenna_name,

manufacturer_id,

connectortype_id)

VALUES ('rubber duck',

2,

2)

 

ON CONFLICT (antenna_name,

manufacturer_id)

DO

UPDATE

SET

connectortype_id = excluded.connectortype_id;

Этот код добавит определенную антенну или обновит существующую антенну, но только для той строки, где вы нарушили уникальный индекс, который имеется у таблицы на двух столбцах, antenna_name и manufacturer_id. Короче говоря, это способ сделать оператор MERGE без самого оператора MERGE. Я не уверен, влияет это на производительность, что имеет место при использовании MERGE в SQL Server. Вы можете также использовать предложение DO NOTHING, чтобы предотвратить ответ при конфликте.

Это почти все о синтаксисе оператора INSERT, хотя есть еще кое-что. Если вы хотите узнать больше о дополнительных функциях, загляните в документацию PostgreSQL на оператор INSERT.

UPDATE

Если пришло время изменить данные в базе данных, вы будете использовать оператор UPDATE в ожидаемой форме:

UPDATE

radio.bands

SET

band_name = '70 CM'

WHERE

band_id = 2;

Это оператор UPDATE. Затем вы указываете таблицу и, да, так же, как вы можете добавлять данные только в единственную таблицу за один раз, вы можете модифицировать данные только в одной таблице в данный момент времени. Команда SET позволяет затем вам выбрать столбцы, которые вы собираетесь изменить. Наконец, предложение WHERE используется для фильтрации данных, ограничивая строки для модификации.

Без предложения WHERE будут изменены все строки в таблице.

Вы также можете использовать предложение FROM для изменения данных между таблицам, например, так:

UPDATE

radio.bands

SET

band_name = r.radio_name

FROM

radio.radios AS r

WHERE

band_id = r.radio_id;

Еще одно замечание относительно UPDATE. Поскольку в PostgreSQL имеется возможность наследования таблиц, в операторы UPDATE может быть добавлено дополнительное предложение для гарантии, что только в указанной таблице должны изменяться данные. (Обсуждение наследования выходит за рамки этой статьи, но в документации PostgreSQL имеется простое объяснение):

UPDATE ONLY

radio.bands

SET

band_name = r.radio_name

FROM

radio.radios AS r

WHERE

band_id = r.radio_id;

За исключением добавления предложения ONLY, этот оператор UPDATE ничем не отличается от приведенного выше. Однако теперь, если radio.bands наследует от другой таблицы, этот оператор гарантирует, что только указанная таблица будет обрабатываться.

DELETE

С командой DELETE мы наконец-то имеем несколько больше отклонений от стандарта, хотя стандартные вещи тоже присутствуют. Простой оператор, подобный следующему, удалит все данные в таблице:

DELETE

FROM

radio.bands;

Как и в случае SQL Server, вы можете также использовать команду TRUNCATE для более быстрого удаления данных из таблицы. TRUNCATE имеет ограничения (например, разрешение, и он не будет работать с ограничениями FOREIGN KEY). На самом деле, если вы пытаетесь выполнить этот оператор DELETE, то получите ошибку, поскольку она вызывается ограничением внешнего ключа.

У TRUNCATE есть преимущества для больших обновлений, таких как удаление всех версий строк без необходимости выполнения процесса VACUUM (Подробнее о процессе VACUUM смотрите в статье Henrietta Dombrovskaya.

Если вы хотите быть более конкретны, то получите преимущество от предложения WHERE:

DELETE

FROM

radio.antenna

WHERE

antenna_id = 42;

Это примерно то, что я ожидал. Один момент, точно так же, как вы должны были обязательно использовать предложение INTO в операторе INSERT, вы должны использовать ключевое слово FROM в DELETE. И, если вы хотите сослаться на другую таблицу, вы не можете просто начать писать операторы JOIN, как это делалось бы в T-SQL. Вместо этого вы должны вставить предложение USING:

DELETE

FROM

radio.antennabands AS ab

USING radio.bands AS b

WHERE

ab.band_id = b.band_id

AND b.band_name = '6 Meters';

Итак, здесь я использовал алиасы для обеих таблиц antennabands и bands. Затем в предложении WHERE я определил критерий соединения таблиц и критерий фильтрации для band_name.

Дополнительно к этому у вас также есть предложение ONLY, чтобы определиться с наследованием.

Предложение RETURNING

Один момент поведения, который является общим для всех стандартных команд манипулирования данными, — это предложение RETURNING. По сути он возвращает результирующий набор, данные, которые были созданы или изменены. Поэтому в примере с INSERT вы не могли увидеть нечего помимо того, что вы предоставили, за исключением использованных значений по умолчанию, таких как номер последовательности в столбце identity. Затем вы можете получить значение или значения для вставки множества сгенерированных строк.

Для оператора UPDATE предложение RETURNING покажет вам значения строки или строк, которые были удалены из таблицы.

Предложение RETURNING можно использовать для действительно интересных вещей, особенно потому, что вы можете использовать общие табличные выражения (CTE) со всеми этими запросами манипуляции данными. Это позволяет делать нечто подобное:

WITH addant AS

(

/* Создаем новую строку в antenna */

INSERT

INTO

radio.antenna (antenna_name,

manufacturer_id,

connectortype_id)

VALUES('Rubber duck',

1,

2)

RETURNING antenna_id)

/*

возьмем новое значение antenna_id и создадим

новую строку в antennabands

*/

INSERT

INTO

radio.antennabands 

(antenna_id,

band_id)

SELECT

aa.antenna_id,

1

FROM

addant AS aa;

Предложение WITH определяет набор строк, addant, которым является лишь значение RETURNING для antenna_id, сгенерированного в операторе INSERT, который определяет CTE. Я могу затем использовать это значение для добавления еще одной строки в другую таблицу в рамках единого оператора. Это открывает множество возможностей для связанных операторов, которые могут выполняться как единый оператор.

MERGE

Как и в SQL Server, команда MERGE дает вам возможность комбинировать операторы INSERT, UPDATE и DELETE в различных сочетаниях в рамках единого оператора. При использовании MERGE вы оцениваете условия, чтобы определить поведение. Это означает, что вы всегда будете иметь целевую таблицу, с которой будут выполняться действия. Кроме того, вам нужно иметь источник оцениваемых данных. Это может быть таблица или набор таблиц, определяемых в подзапросе, который необходим для оценки необходимых действий. Здесь вы можете определить WHEN MATCHED для условий, которые требуют совпадающее значение, или WHEN NOT MATCHED для противоположных условий. Порядок, в котором вы их определяете, является порядком, в котором они будут оцениваться.

Важно отметить, что команда MERGE появилась в PostgreSQL 15.

Вот пример. Я помещу некоторые данные по временную таблицу, а затем использую логику для добавления данных, обновления или удаления их на основе переданной информации:

CREATE TEMPORARY TABLE radioupdates (radio_name varchar(100),

manufacturer_id int,

connectortype_id int,

delete_flag int);

INSERT

INTO

radioupdates (radio_name,

manufacturer_id,

connectortype_id,

delete_flag)

VALUES

('UV5R',

2,

3,

0),

('UV5R',

3,

3,

0),

('UV5R',

1,

3,

1);

Затем я буду использовать следующий оператор MERGE для слияния значений из временной таблицы в таблицу radios:

MERGE

INTO

radio.radios AS r

USING radioupdates AS ru

ON

ru.radio_name = r.radio_name

AND ru.manufacturer_id = r.manufacturer_id

WHEN NOT MATCHED THEN

INSERT

VALUES(DEFAULT,

ru.radio_name,

ru.manufacturer_id,

NULL,

ru.connectortype_id,

NULL)

WHEN MATCHED

AND ru.delete_flag = 0 THEN

UPDATE

SET

connectortype_id = ru.connectortype_id

WHEN MATCHED

AND ru.delete_flag = 1 THEN

DELETE;

Хитрость заключается в том, чтобы правильно понять логику. В моем операторе MERGE используется следующая логика: если нет совпадения на основе критерия ON, WHEN NOT MATCHED, я буду вставлять строку. Затем при совпадении, но не установленном флаге на удаление, будет выполняться оператор UPDATE для значений в источнике. В противном случае, если есть совпадение и установлен флажок удаления, выполняется удаление.

Вы можете также указать DO NOTHING по результатам оценок. Ключевое слово ONLY можно использовать для случая наследования, о чем говорилось в разделе UPDATE. Вы даже можете использовать OVERRIDING в предложении INSERT. Короче говоря, большинство вариантов поведения, которые были рассмотрены в этой статье, также применмы к MERGE.

Просто напомню, старый подход заключался в использовании предложения ON CONFLICT, чтобы получить команду типа UPSERT (UPDATE или INSERT). Этот метод не обладает мощью MERGE, когда вам необходима дополнительная сложность, например, удаления данных из целевой таблицы.

Все подробности об операторе MERGE моджно найти в документации PostgreSQL.

Заключение

Манипуляция данными в PostgreSQL является одной из самых простых вещей, которые я изучил до настоящего времени. По большей части они аналогичны операторам в SQL Server. Хотя имеются некоторые различия в деталях, это было то поведение, которое я ожидал получить. Мне действительно нравится, как можно использовать общие табличные выражения с этими командами. Мне также понравилась работа RETURNING, которая позволяет вам обеспечить действительно уникальное поведение. В целом, в PostgreSQL есть довольно много полезной функциональности, когда речь идет о непосредственном манипулировании данными.


Ссылки по теме
1. Вставка строк в таблицу, содержащую автоинкрементируемое поле
2. Работа со столбцами identity в SQL Server
3. Заполнение столбца SQL Server последовательным номерами без использования identity
4. Оператор TRUNCATE TABLE