28 ms·

Could someone give some examples on their own domains where the MERGE command is a huge QOL improvement over what's currently available? I see a lot of people

Could someone give some examples on their own domains where the MERGE command is a huge QOL improvement over what's currently available? I see a lot of people being so very happy in the comments, and, well, I've tried to think long and hard about how to apply it to my current domain but was a bit at a loss... Maybe some practical examples can help?

elchief 4y ago
it's common to use it to load Slowly Changing Dimensions in data warehouses, at least in other systems, so it's nice to have the same-ish syntax in PG
NegativeLatency 4y ago
Works well for bulk operations where you're loading data in on a lower frequency.
gen220 4y ago
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 4y ago
  You can already do SELECT followed by UPDATE/INSERT in a single query in postgres using CTEs...
  - gen220 4y ago
    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 4y ago
      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 4y ago
        Woah! I stand corrected, thanks. It seems you’re knowledgeable on this, do you know if MERGE useful beyond expressivity?

elchief 4y ago

it's common to use it to load Slowly Changing Dimensions in data warehouses, at least in other systems, so it's nice to have the same-ish syntax in PG

NegativeLatency 4y ago

Works well for bulk operations where you're loading data in on a lower frequency.

gen220 4y ago

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 4y ago
You can already do SELECT followed by UPDATE/INSERT in a single query in postgres using CTEs...
- gen220 4y ago
  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 4y ago
    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 4y ago
      Woah! I stand corrected, thanks. It seems you’re knowledgeable on this, do you know if MERGE useful beyond expressivity?