Example of Using RETURNING with ON CONFLICT in PostgreSQL

2024-10-06

Understanding RETURNING and ON CONFLICT

ON CONFLICT: This clause is used to handle conflicts that may arise during insert operations when rows with unique constraints or indexes already exist. It allows you to specify a specific action to take, such as updating the existing row or throwing an error.
RETURNING: This clause is used to return specific columns from the inserted or updated row back to the client. It's particularly useful for obtaining the generated values of sequences or other automatically updated fields.

Using RETURNING with ON CONFLICT

The general syntax for using RETURNING with ON CONFLICT is:

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

Here's a breakdown of the components:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...): This is the standard INSERT statement, specifying the table and values to be inserted.
ON CONFLICT (unique_constraint_or_index): This clause specifies the unique constraint or index that might cause a conflict.
DO UPDATE SET column1 = excluded.column1, column2 = excluded.column2, ...: This specifies the update action to be performed if a conflict occurs. The excluded keyword refers to the values that would have been inserted if there were no conflict.
RETURNING column1, column2, ...: This clause returns the specified columns from the inserted or updated row back to the client.

Example:

Consider a table named users with columns id, name, and email. The id column is a primary key, and the email column is unique.

INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]')
ON CONFLICT (email)
DO UPDATE SET
    name = excluded.name
RETURNING id, name, email;

In this example:

The RETURNING clause will return the id, name, and email of the inserted or updated user.
If a user with that email already exists, the existing user's name will be updated to John Doe.
If there's no existing user with the email [email protected], a new user will be inserted.

Key Points:

The excluded keyword refers to the values that would have been inserted if there were no conflict.
The ON CONFLICT clause provides a powerful way to handle potential conflicts during insert operations.
The RETURNING clause is optional but can be very useful for obtaining the generated values of sequences or other automatically updated fields.

Example of Using `RETURNING` with `ON CONFLICT` in PostgreSQL

Scenario:

We have a users table with columns id, name, and email. The id column is a serial primary key, and the email column is unique. We want to insert a new user, but if the email already exists, we want to update the user's name. We also want to return the updated or inserted user's information.

SQL Query:

INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]')
ON CONFLICT (email) DO UPDATE SET
    name = excluded.name
RETURNING id, name, email;

Explanation:

INSERT INTO users (name, email): This starts an insertion into the users table.
VALUES ('John Doe', '[email protected]'): These are the values to be inserted into the name and email columns.
ON CONFLICT (email): This specifies that if there's a conflict on the email column (i.e., the email already exists), the following action should be taken.
DO UPDATE SET name = excluded.name: If there's a conflict, the existing row will be updated. The name column will be set to the name value that was trying to be inserted (represented by excluded.name).
RETURNING id, name, email;: This returns the id, name, and email columns of the inserted or updated row.

Breakdown of the RETURNING clause:

In this example, we're returning the id, name, and email columns, which will give us information about the user that was inserted or updated.
The RETURNING clause allows you to retrieve specific columns from the inserted or updated row.

The excluded keyword refers to the values that were excluded from the insertion due to the conflict.
The RETURNING clause is particularly useful when dealing with auto-generated sequences like the id column in this example.

Alternative Methods for Upserts in PostgreSQL

While the RETURNING clause with ON CONFLICT provides a concise and efficient way to perform upserts in PostgreSQL, there are other approaches you can consider depending on your specific requirements and preferences:

Using INSERT OR UPDATE (PostgreSQL 9.5 and later):

This syntax offers a more straightforward way to perform upserts:

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

The behavior is identical to the RETURNING method, but you don't explicitly return any columns. If you need to retrieve specific columns, you can use a separate SELECT query after the upsert.

Using a MERGE Statement (PostgreSQL 14 and later):

The MERGE statement is a more general-purpose upsert mechanism that provides greater flexibility:

MERGE INTO table_name AS target
USING source_table AS source
ON target.column = source.column
WHEN MATCHED THEN UPDATE SET
    target.column1 = source.column1,
    target.column2 = source.column2,
    ...
WHEN NOT MATCHED THEN INSERT (column1, column2, ...)
VALUES    (source.column1, source.column2, ...);

This approach is particularly useful when you have a source table that contains the data to be inserted or updated. It allows you to specify different update and insert actions based on whether a matching row exists.

Using a DO $$ Block with PL/pgSQL:

If you need more complex logic or want to perform multiple operations within a single transaction, you can use a DO $$ block with PL/pgSQL:

DO $$
BEGIN
    INSERT INTO table_name (column1, column2, ...)
    VALUES (value1, value2, ...)
    ON CONFLICT (unique_constraint_or_index) DO UPDATE SET
        column1 = excluded.column1,
        column2 = excluded.column2,
        ...;

    -- Additional PL/pgSQL code here
END $$;

This approach gives you more control over the upsert process and allows you to incorporate other PL/pgSQL features like conditional logic, loops, and functions.

Choosing the Right Method:

The best method for your upsert operations depends on several factors, including:

Readability and maintainability: Consider which method is easier to understand and maintain for your team.
Performance requirements: The RETURNING method is generally efficient, but other methods might have different performance characteristics.
Complexity of the upsert logic: If you need complex logic, PL/pgSQL might be more suitable.
PostgreSQL version: Some methods are only available in specific versions.

sql postgresql upsert