PostgreSQL - Upsert

In PostgreSQL, "upsert" is an informal term used to describe the process of inserting a new record if it doesn't exist, or updating the record if it does exist. The actual SQL command that provides this capability in PostgreSQL is INSERT ... ON CONFLICT.

The ON CONFLICT clause helps handle unique constraint violations. This can be useful, for instance, when you're dealing with scenarios where you might be receiving data in a non-linear fashion, or where records can be updated and inserted in an arbitrary order.

Syntax:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target)
DO NOTHING;

-- OR

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target)
DO UPDATE SET column1 = value1, column2 = value2, ...;

Where:

conflict_target can be a column or a set of columns that have a unique constraint or a unique index.
The DO NOTHING action means no action will be taken if there's a conflict.
The DO UPDATE action allows you to specify how the existing row should be updated if there's a conflict.

Examples:

Insert or Do Nothing:
Suppose you have a table users with a unique constraint on the email column. If you want to insert a new user but do nothing if the user's email already exists:
```
INSERT INTO users (name, email)
VALUES ('John Doe', 'johndoe@example.com')
ON CONFLICT (email)
DO NOTHING;
```
Insert or Update:
Using the same users table, if you want to insert a new user or update the user's name if the email already exists:
```
INSERT INTO users (name, email)
VALUES ('John Doe', 'johndoe@example.com')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;
```
Here, the EXCLUDED keyword represents the values of the row proposed for insertion. So, EXCLUDED.name refers to 'John Doe' from our VALUES clause.

Notes:

When using ON CONFLICT, ensure that the target table has a unique constraint or unique index on the conflict_target column(s). Otherwise, the upsert will not work as intended.
It's important to remember that upserts are atomic in PostgreSQL, meaning they'll either completely succeed or completely fail. This ensures data consistency even when multiple transactions are trying to upsert on the same rows simultaneously.

In summary, "upsert" operations in PostgreSQL (using the INSERT ... ON CONFLICT construct) allow for more flexible data insertion and updating by handling potential conflicts in a specified manner. This capability can be especially useful in scenarios where data can come in an unpredictable order or from multiple sources.

PostgreSQL upsert example: Perform an upsert using the INSERT ON CONFLICT statement:

INSERT INTO example_table (id, value)
VALUES (1, 'new_value')
ON CONFLICT (id) DO UPDATE SET value = 'updated_value';

How to perform upsert in PostgreSQL: Use the INSERT ON CONFLICT statement to perform an upsert:

INSERT INTO target_table (id, column1, column2)
VALUES (1, 'value1', 'value2')
ON CONFLICT (id) DO UPDATE SET column1 = 'updated_value1', column2 = 'updated_value2';

Upsert with unique constraint in PostgreSQL: Perform an upsert with a unique constraint:

INSERT INTO example_table (id, value)
VALUES (1, 'new_value')
ON CONFLICT ON CONSTRAINT unique_constraint_name DO UPDATE SET value = 'updated_value';

Conflict resolution in PostgreSQL upsert: Define conflict resolution with the ON CONFLICT clause:
```
INSERT INTO example_table (id, value)
VALUES (1, 'new_value')
ON CONFLICT (id) DO NOTHING;
```

Upsert with multiple conditions in PostgreSQL: Perform an upsert with multiple conflict conditions:

INSERT INTO example_table (id, value)
VALUES (1, 'new_value')
ON CONFLICT (id, column2) DO UPDATE SET value = 'updated_value';

Returning values after upsert in PostgreSQL: Return values after an upsert operation:

INSERT INTO example_table (id, value)
VALUES (1, 'new_value')
ON CONFLICT (id) DO UPDATE SET value = 'updated_value'
RETURNING *;

Batch upsert in PostgreSQL: Perform a batch upsert with the VALUES clause:

INSERT INTO example_table (id, value)
VALUES (1, 'new_value1'), (2, 'new_value2')
ON CONFLICT (id) DO UPDATE SET value = EXCLUDED.value;

Upsert vs MERGE in PostgreSQL: Compare upsert and MERGE (upsert-like) operations:

MERGE INTO target_table USING source_table
ON target_table.id = source_table.id
WHEN MATCHED THEN UPDATE SET target_table.column1 = source_table.column1
WHEN NOT MATCHED THEN INSERT (id, column1, column2) VALUES (source_table.id, source_table.column1, source_table.column2);