8 ms·

They could have consolidated the syntax for both, though. No reason you couldn't have this instead: INSERT ... VALUES (C1=v1, C2=v2), (C1=v3, C2=v4), (C1=v

They could have consolidated the syntax for both, though. No reason you couldn't have this instead: INSERT ... VALUES (C1=v1, C2=v2), (C1=v3, C2=v4), (C1=v5, C2=v6) This would actually be more powerful because it would allow you to arbitrarily omit columns on a per-row basis, which you can't do with SQL today.

smallnamespace 4y ago
You can omit columns on a per-row basis with the DEFAULT keyword, it's just more cumbersome if your rows are very sparse. Your proposed syntax of: INSERT ... VALUES (C1=v1, C2=v2), (C3=v3, C4=v4), (C5=v5, C6=v6) in standard SQL is INSERT ... (C1, C2, ... C6) VALUES (v1, v2, DEFAULT, DEFAULT, DEFAULT, DEFAULT), (DEFAULT, DEFAULT, v3, v4, DEFAULT, DEFAULT), ...
layer8 4y ago
It would maybe be more powerful, but also less efficient. If you want to insert different sets of columns, you just use multiple INSERT statements. The point of the current syntax is to be able to efficiently bulk-insert many rows using the same set of columns. That set of columns needs to be validated and mapped to the internal offsets (or whatever) only once for inserting an arbitrarily large number of rows, instead of having to repeat that validation and preprocessing for each row.
- NavinF 4y ago
  What a bizarre use case. I highly doubt your scheme performs better than just using PREPARE AS INSERT to create a prepared statement and execution plan on the database side. Or are we still generating SQL using string interpolation in 2022?
  - ipaddr 4y ago
    Most people have an orm between them and the database. Think bulk inserting into via the command why the format exists vs what you do in your language and/or framework.
    - NavinF 4y ago
      If you call row.save() on one ORM object at a time, you'll get neither the performance benefits of prepared statements nor that of multi-row inserts. If you save many objects at a time, the ORM library will use prepared statements.
  - regularfry 4y ago
    We're certainly still slurping up bulk SQL commands from raw text files in 2022. Kinda odd not to come across that use case.
  - Shorel 4y ago
    I think it is pretty standard usage for ETL tools. You are not inserting a few dozen records, you are inserting a several thousand records each query. In fact, sometimes insert speed is measured in MB per second, not records per second.
- wruza 4y ago
  And the point of gp is that SQL could do some work and sort these (a=b) values into groups that fit its inner requirements on its own. You can just omit a useful feature because it creates a minor inconvenience at a preparation phase, or you can include it. This is exactly these bad ergonomics some grand-commenter talked about.
lelanthran 4y ago
They still aren't identical constructs, because the intended action is very different; multiple insertions result in multiple distinct records all with different values while multiple updates result in multiple records with the same new values Having the syntax identical is inferior in every way except for the case where you want to build the string up programmatically, and that is a trivial problem to solve for the caller. I think your proposal works better for an UPSERT keyword, as that is yet another different intended action.

smallnamespace 4y ago

You can omit columns on a per-row basis with the DEFAULT keyword, it's just more cumbersome if your rows are very sparse. Your proposed syntax of: INSERT ... VALUES (C1=v1, C2=v2), (C3=v3, C4=v4), (C5=v5, C6=v6) in standard SQL is INSERT ... (C1, C2, ... C6) VALUES (v1, v2, DEFAULT, DEFAULT, DEFAULT, DEFAULT), (DEFAULT, DEFAULT, v3, v4, DEFAULT, DEFAULT), ...

layer8 4y ago

It would maybe be more powerful, but also less efficient. If you want to insert different sets of columns, you just use multiple INSERT statements. The point of the current syntax is to be able to efficiently bulk-insert many rows using the same set of columns. That set of columns needs to be validated and mapped to the internal offsets (or whatever) only once for inserting an arbitrarily large number of rows, instead of having to repeat that validation and preprocessing for each row.

NavinF 4y ago
What a bizarre use case. I highly doubt your scheme performs better than just using PREPARE AS INSERT to create a prepared statement and execution plan on the database side. Or are we still generating SQL using string interpolation in 2022?
- ipaddr 4y ago
  Most people have an orm between them and the database. Think bulk inserting into via the command why the format exists vs what you do in your language and/or framework.
  - NavinF 4y ago
    If you call row.save() on one ORM object at a time, you'll get neither the performance benefits of prepared statements nor that of multi-row inserts. If you save many objects at a time, the ORM library will use prepared statements.
- regularfry 4y ago
  We're certainly still slurping up bulk SQL commands from raw text files in 2022. Kinda odd not to come across that use case.
- Shorel 4y ago
  I think it is pretty standard usage for ETL tools. You are not inserting a few dozen records, you are inserting a several thousand records each query. In fact, sometimes insert speed is measured in MB per second, not records per second.
wruza 4y ago
And the point of gp is that SQL could do some work and sort these (a=b) values into groups that fit its inner requirements on its own. You can just omit a useful feature because it creates a minor inconvenience at a preparation phase, or you can include it. This is exactly these bad ergonomics some grand-commenter talked about.

lelanthran 4y ago

They still aren't identical constructs, because the intended action is very different; multiple insertions result in multiple distinct records all with different values while multiple updates result in multiple records with the same new values Having the syntax identical is inferior in every way except for the case where you want to build the string up programmatically, and that is a trivial problem to solve for the caller. I think your proposal works better for an UPSERT keyword, as that is yet another different intended action.