Как применить ON CONFLICT для отслеживания изменений при INSERT?

Question

nurzhannogerbek @nurzhannogerbek

Как применить ON CONFLICT для отслеживания изменений при INSERT?

Здравствуйте, товарищи! Помогите пожалуйста разобраться.

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

| survey_id                            | employee            | status (default: false) |
|--------------------------------------|---------------------|-------------------------|
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | NNogerbek@gmail.com | true                    |
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | DJony@gmail.com     | true                    |

Следующий SQL запрос создает новые 2 записи в таблице:

INSERT INTO SURVEYS_EMPLOYEES_RELATIONSHIP (SURVEY_ID, EMPLOYEE)
SELECT '66c89a34-fff2-4cbc-a542-b1e956a352f3' SURVEY_ID, EMPLOYEE FROM UNNEST(ARRAY['NNogerbek@gmail.com', 'IKim@gmail.com']) EMPLOYEE

В итоге в таблице у меня получается 4 записи:

| survey_id                            | employee            | status  |
|--------------------------------------|---------------------|---------|
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | NNogerbek@gmail.com | true    |
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | DJony@gmail.com     | true    |
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | NNogerbek@gmail.com | false   |
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | IKim@gmail.com      | false   |

Возможно ли не встравлять в таблицу записи, которые уже присутствуют в таблице? Как вы можете видеть в таблице уже есть идентичные записи по столбцам "survey_id", "employee". Это запись с email "NNogerbek@gmail.com". При этом нужно удалить из таблицы записи, которых не было при INSERT. Это запись с email "DJony@gmail.com" получается. В финале хочу получить такой результат:

| survey_id                            | employee            | status  |
|--------------------------------------|---------------------|---------|
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | NNogerbek@gmail.com | true    |
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | IKim@gmail.com      | false   |

Насколько я знаю, есть ON CONFLICT, но я не уверен применим ли он в моем случаи. Точнее для обоих условий.

Вопрос задан более трёх лет назад
124 просмотра

Комментировать

Подписаться 1 Простой Комментировать

Решения вопроса 2

Комментировать

2 комментария

nurzhannogerbek @nurzhannogerbek Автор вопроса

Здравствуйте, Константин!

Пытаюсь объединить два пункта в одну процедуру, как вы и советовали. Я написал такую процедуру:

CREATE OR REPLACE PROCEDURE creator(SURVEY_ID uuid, EMPLOYEES VARCHAR[]) AS $FUNCTION$
	BEGIN
		DELETE FROM SURVEYS_EMPLOYEES_RELATIONSHIP
		WHERE SURVEY_ID = SURVEY_ID
		AND EMPLOYEE NOT IN (EMPLOYEES);
		--
		INSERT INTO SURVEYS_EMPLOYEES_RELATIONSHIP (SURVEY_ID, EMPLOYEE) 
		SELECT SURVEY_ID SURVEY_ID, EMPLOYEE FROM UNNEST(ARRAY[EMPLOYEES]) EMPLOYEE
		ON CONFLICT ON CONSTRAINT unique_key 
		DO NOTHING;
	END;
$FUNCTION$ LANGUAGE plpgsql;

Вызываю эту процедуру следующим образом:

CALL creator('99c89a24-fff2-4cbc-a542-b1e956a352f9', ARRAY['NNogerbek@gmail.com', 'IKim@gmail.com'])

При вызове происходит ошибка:

SQL Error [42702]: ERROR: column reference "survey_id" is ambiguous
Подробности: It could refer to either a PL/pgSQL variable or a table column.
Где: PL/pgSQL function creator(uuid,character varying[]) line 3 at SQL statement

Где в процедуре я ошибся?

Написано более трёх лет назад

Константин Цветков @tsklab

NogerbekNurzhan, SURVEY_ID = SURVEY_ID.

Написано более трёх лет назад

Пригласить эксперта

Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации

Похожие вопросы

SQL

Простой
Как правильно работать с двумя версиями данных в ClickHouse?
- 1 подписчик
- 13 часов назад
- 18 просмотров
0

ответов
PHP

+1 ещё

Простой
Где ошибка в коде при создании древа в sql из файла?
- 1 подписчик
- вчера
- 97 просмотров
0

ответов
PostgreSQL

Простой
Нужен ли первичный ключ в таблицах PostgreSQL?
- 1 подписчик
- 23 апр.
- 147 просмотров
2

ответа
PostgreSQL

+1 ещё

Простой
Как добавить отношения «многие-ко-многим» между таблицами из разных баз данных?
- 1 подписчик
- 22 апр.
- 131 просмотр
4

ответа
MySQL

+1 ещё

Простой
Как извлечь топ 15 очков из таблицы чтобы игроки не дублировались?
- 1 подписчик
- 22 апр.
- 118 просмотров
1

ответ
PostgreSQL

+1 ещё

Простой
Как исправить неправильное отображение данных в csv после экспорта?
- 1 подписчик
- 17 апр.
- 106 просмотров
1

ответ
Java

+3 ещё

Средний
Пытаюсь подключиться к postgresql 16 через docker-compose, использую spring-boot 3.2.4, что не так?
- 1 подписчик
- 17 апр.
- 201 просмотр
3

ответа
PostgreSQL

+2 ещё

Простой
Где искать рекомендуемые настройки SSL-аутентификации для Docker-образа Posgres?
- 1 подписчик
- 17 апр.
- 65 просмотров
3

ответа
PostgreSQL

+1 ещё

Простой
Как поправить язык в SQL Shell (psql)?
- 1 подписчик
- 14 апр.
- 98 просмотров
1

ответ
Python

+1 ещё

Простой
Как оптимизировать запрос?
- 1 подписчик
- 13 апр.
- 146 просмотров
2

ответа
Показать ещё Загружается…

Тестировщик SQL

Bell Integrator • Санкт-Петербург

До 200 000 ₽

Программист SQL

АвтоАльянс • Москва

от 165 000 ₽

Программист SQL

САМО-Софт • Москва

До 220 000 ₽

Разработать робота для отправки собщений в аське

26 апр. 2024, в 08:24

6500 руб./за проект

Devops для видео сервиса

26 апр. 2024, в 06:46

1000 руб./в час

Найти ошибку flutter_map

26 апр. 2024, в 05:31

1000 руб./за проект

Answer 1 · 2019-04-01 16:42:09

Возможно ли не встравлять в таблицу записи, которые уже присутствуют в таблице?

Возможно. Если вы этим знанием свой предметной области поделитесь с СУБД и создадите уникальное ограничение. А скорей всего это вовсе натуральный первичный ключ.

on conflict работает как ни удивительно при конфликте уникальности. Поэтому пока у вас никто не гарантирует уникальность данных - on conflict ничего не сделает.

Answer 2 · 2019-04-01 17:01:31

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

Как применить ON CONFLICT для отслеживания изменений при INSERT?

Войдите, чтобы написать ответ

Минуточку внимания

Войдите на сайт