PostgreSQL, one such feature that distinguishes more advanced users is the MERGE command, introduced in version 15 and expanded in version 17 (in beta at the time of writing this article). Before MERGE, developers typically relied on INSERT ... ON CONFLICT DO UPDATE for upserts—a method introduced in PostgreSQL 9.5 that has since become a staple in many developers' toolkits.
While ON CONFLICT offers a straightforward solution for simple upsert scenarios, it can quickly become limiting as business logic grows in complexity. This is where the MERGE command excels. Introduced in the SQL:2003 standard, MERGE allows for more sophisticated data synchronisation tasks by combining multiple operations—such as conditional inserts, updates, and deletes—into a single, atomic statement.
PostgreSQL, one such feature that distinguishes more advanced users is the
MERGE
command, introduced in version 15 and expanded in version 17 (in beta at the time of writing this article). BeforeMERGE
, developers typically relied onINSERT ... ON CONFLICT DO UPDATE
for upserts—a method introduced in PostgreSQL 9.5 that has since become a staple in many developers' toolkits.While
ON CONFLICT
offers a straightforward solution for simple upsert scenarios, it can quickly become limiting as business logic grows in complexity. This is where theMERGE
command excels. Introduced in the SQL:2003 standard,MERGE
allows for more sophisticated data synchronisation tasks by combining multiple operations—such as conditional inserts, updates, and deletes—into a single, atomic statement.Beyond Simple Upserts with MERGE in PostgreSQL