How to use ON CONFLICT to track changes on INSERT?

Question

PostgreSQL

Hello comrades! Please help me figure it out.
The PostgreSQL database has a table with the following structure:

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

The following SQL query creates 2 new records in the table:

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

As a result, in the table I get 4 records:

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

Is it possible to not insert records into a table that are already present in the table? As you can see, the table already has identical entries in the "survey_id", "employee" columns. This is an entry from the email "[email protected]". In this case, you need to remove records from the table that were not during INSERT. This is the entry from the email "[email protected]" is obtained. In the end, I want to get the following result:

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

As far as I know, there is ON CONFLICT , but I'm not sure if it is applicable in my case. More precisely for both conditions.

Answer 1

Is it possible to not insert records into a table that are already present in the table?

Maybe. If you share this knowledge of your subject area with the DBMS and create a unique constraint. And most likely it is a natural primary key.
on conflict works surprisingly well for uniqueness conflicts. Therefore, while no one guarantees the uniqueness of the data, on conflict will do nothing.