High Performance PostgreSQL for Rails

Upserts with SQL MERGE

PostgreSQL 15 added support for SQL MERGE,^[324] which is a new command that you’ll explore in this section.

MERGE supports two clauses:

• WHEN MATCHED THEN
• WHEN NOT MATCHED THEN

These clauses describe how to handle matches and no matches. Let’s look at an example.

Using the same temp.people table you created earlier with Jane and Bob as rows, write a SQL statement like this:

sql/merge_when_matched_then.sql

MERGE INTO temp.people p
  USING ( VALUES(2, 'Jill') ) people_data
  ON people_data.column1 = p.id
WHEN NOT MATCHED THEN
  INSERT VALUES(people_data.column1, people_data.column2)
WHEN MATCHED THEN
  UPDATE SET name = people_data.column2;

Let’s review the details. In this statement, the ...