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

Question

[nurzhannogerbek]

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

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

| survey_id                            | employee            | status (default: false) |
|--------------------------------------|---------------------|-------------------------|
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | [e-mail] | true                    |
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | [e-mail]     | true                    |

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

INSERT INTO SURVEYS_EMPLOYEES_RELATIONSHIP (SURVEY_ID, EMPLOYEE)
SELECT '66c89a34-fff2-4cbc-a542-b1e956a352f3' SURVEY_ID, EMPLOYEE FROM UNNEST(ARRAY['[e-mail]', '[e-mail]']) EMPLOYEE

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

| survey_id                            | employee            | status  |
|--------------------------------------|---------------------|---------|
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | [e-mail] | true    |
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | [e-mail]     | true    |
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | [e-mail] | false   |
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | [e-mail]      | false   |

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

| survey_id                            | employee            | status  |
|--------------------------------------|---------------------|---------|
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | [e-mail] | true    |
| 66c89a34-fff2-4cbc-a542-b1e956a352f3 | [e-mail]      | false   |

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

Answer 1

[Melkij]

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

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

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

Answer 2

[Константин Цветков]

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

Comments

[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['[e-mail]', '[e-mail]'])

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

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

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

[Константин Цветков]

NogerbekNurzhan, SURVEY_ID = SURVEY_ID.