[gen220]

The operation it's replacing is something like "SELECT, followed by UPDATE/INSERT".

Implicit in that sequence is transmitting the selected rows over the network, and buffering the rows in-memory on the client side.

With MERGE, you eliminate the network stress, and push the burden of managing the rows in-memory onto the postgres server.

That's quite nice if you have beefy operations and want to keep the services/jobs running those operations lean.

[nicoburns]

You can already do SELECT followed by UPDATE/INSERT in a single query in postgres using CTEs...

[gen220]

You can do them individually yes, but you can’t do INSERT and UPDATE from the same SELECT CTE.

Before, you’d have to either load the data in the client side or duplicate the CTE across two statements in a transaction.

[nicoburns]

You can, like this:

    WITH results as (
      SELECT ...
    ),

    inserted_rows AS (
      INSERT ...
      SELECT * FROM results
      ...
    ),

    deleted_rows AS (
      DELETE ...
      USING results
      ... 
    )

    UPDATE ...
    FROM results

[gen220]

Woah! I stand corrected, thanks.

It seems you’re knowledgeable on this, do you know if MERGE useful beyond expressivity?