SQL-Ex blog

Поддержка соединений в DELETE

PostgreSQL не поддерживает оператор DELETE JOIN. Но мы можем использовать предложение USING с оператором DELETE, чтобы достичь той же функциональности.

Синтаксис выглядит следующим образом:



Здесь:

• После предложения USING указывается табличное выражение, которым может быть одна или даже больше таблиц (эквивалентно Join).


• В предложении WHERE используются столбцы из таблиц в предложении USING для соединения данных (эквивалентно условию в join)

Решение для нашего сценария примера

Для решения задачи, которую мы обсуждали, нам потребуется использовать DELETE с USING. Сначала нам нужно создать следующие таблицы:

DROP TABLE IF EXISTS ALL_EMPLOYEES;

CREATE TABLE ALL_EMPLOYEES(

   employee_id serial PRIMARY KEY,

   first_name varchar(50) NOT NULL,

   last_name varchar(50) NOT NULL,

   department varchar(15) NOT NULL

);

DROP TABLE IF EXISTS SEP_EMPLOYEES;

CREATE TABLE SEP_EMPLOYEES(

    employee_id INT NOT NULL UNIQUE

);

Теперь давайте добавим несколько фиктивных данных в эти таблицы:

INSERT INTO ALL_EMPLOYEES (first_name, last_name, department) VALUES ('Sabyasachi', 'Mukherjee', 'IT Analyst');

INSERT INTO ALL_EMPLOYEES (first_name, last_name, department) VALUES ('Stephen', 'Johnas', 'Senior Dev');

INSERT INTO ALL_EMPLOYEES (first_name, last_name, department) VALUES ('David', 'Willson', 'DBA');

INSERT INTO SEP_EMPLOYEES VALUES (1);

INSERT INTO SEP_EMPLOYEES VALUES (2);

INSERT INTO SEP_EMPLOYEES VALUES (4);

Теперь, когда нам нужно удалить данные таблицы SEP_EMPLOYEES из ALL_EMPLOYEES, мы можем написать:

DELETE FROM ALL_EMPLOYEES

USING SEP_EMPLOYEES

WHERE SEP_EMPLOYEES.EMPLOYEE_ID = ALL_EMPLOYEES.EMPLOYEE_ID

RETURNING ALL_EMPLOYEES.FIRST_NAME || ' ' || ALL_EMPLOYEES.LAST_NAME;

В результате будут возвращены следующие строки:



Они показывают имена, которые были удалены из таблицы ALL_EMPLOYEES. Набор записей в таблице ALL_EMPLOYEES выглядит так:

Сценарий примера

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

Поддержка PostgreSQL UPSERT

UPSERT - это в основном INSERT+UPDATE, т.е. он говорит, что если не обнаруживается подобной записи, то вы можете вставить запись, в противном случае обновить совпадающую существующую запись предоставленной информацией.Чтобы воспользоваться этой функцией, нам потребуется использовать синтаксис INSERT ON CONFLICT, который выглядит так:



target может быть либо именем столбца, либо именем ограничения, либо предложением WHERE. С другой стороны, action_to_be_performed может быть либо оператором DO NOTHING (ничего не делать), либо DO UPDATE (обновить).

Решение для нашего сценария примера

Сначала нам нужно пересоздать таблицу со столбцом текущего адреса:

DROP TABLE IF EXISTS ALL_EMPLOYEES;

CREATE TABLE ALL_EMPLOYEES(

   employee_id serial PRIMARY KEY,

   first_name varchar(50) NOT NULL,

   last_name varchar(50) NOT NULL,

   email_id varchar(50) NOT NULL UNIQUE,

   department varchar(15) NOT NULL,

   present_address varchar(255) NOT NULL

);

Теперь вставим несколько записей в эту таблицу, выполнив следующие запросы:

INSERT INTO ALL_EMPLOYEES (first_name, last_name, email_id, department, present_address) VALUES ('Sabyasachi', 'Mukherjee', 'sabya.mukh@gmail.com', 'IT Analyst', 'India, WB, Kolkata');

INSERT INTO ALL_EMPLOYEES (first_name, last_name, email_id, department, present_address) VALUES ('David', 'Willson', 'david.will@gmail.com', 'DBA', 'USA, New Jersey');

INSERT INTO ALL_EMPLOYEES (first_name, last_name, email_id, department, present_address) VALUES ('John', 'Vancock', 'john.van@gmail.com', 'Senior Dev', 'USA, San Francisco');

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

INSERT INTO 

    ALL_EMPLOYEES (first_name, last_name, email_id, department, present_address)

VALUES 

    ('Sabyasachi', 'Mukherjee', 'sabya.mukh@gmail.com', 'Senior Dev', 'USA, Canada')

ON CONFLICT (email_id)

DO UPDATE

	SET present_address = present_address;

Теперь предположим, что мы получили группу сотрудников, в которую входит 2 новых (с именами Stephen Havard и Anthony Wickwood) и 1 существующий сотрудник (с именем Sabyasachi Mukherjee), запросивший обновление адреса. Тогда сгенерированные массовые запросы INSERT ON CONFLICT будут выглядеть так:

INSERT INTO 

    ALL_EMPLOYEES (first_name, last_name, email_id, department, present_address)

VALUES 

    ('Sabyasachi', 'Mukherjee', 'sabya.mukh@gmail.com', 'Senior Dev', 'USA, Canada')

ON CONFLICT (email_id)

DO UPDATE

	SET present_address = EXCLUDED.present_address;

INSERT INTO 

    ALL_EMPLOYEES (first_name, last_name, email_id, department, present_address)

VALUES 

    ('Stephen', 'Havard', 'stephen.harv@gmail.com', 'Junior Dev', 'Indonesia')

ON CONFLICT (email_id)

DO UPDATE

	SET present_address = EXCLUDED.present_address;	

INSERT INTO 

    ALL_EMPLOYEES (first_name, last_name, email_id, department, present_address)

VALUES 

    ('Anthony', 'Wickwood', 'anthony.wick@gmail.com', 'Finance Auditor', 'USA, New Jersey')

ON CONFLICT (email_id)

DO UPDATE

	SET present_address = EXCLUDED.present_address;

Итак, если мы выполним все эти запросы в пакете, то будут созданы 2 новых сотрудника, а 1 адрес сотрудника должен быть изменен с India, WB, Kolkata на USA, Canada.

В результате получим:



В том случае, если ничего не нужно делать для существующего сотрудника, мы можем использовать DO NOTHING.

Замечание. Мы используем здесь email_id в качестве уникального идентификатора для проверки, является ли сотрудник существующим или новым, поскольку 2 сотрудника не могут иметь один и тот же email_id в организации.

Заключение

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