2023-08-10 honeysql | Clojure Slack Archive

honeysql

joshuamz 2023-08-10T21:18:54.368519Z

Hi. Is it possible to express this syntax (`merge into`) in Honey SQL? I'm using Snowflake as my data storage and I think https://docs.snowflake.com/en/sql-reference/sql/merge. I'm trying to implement an upsert operation with a single query. Thanks!

MERGE INTO t1 USING t2 ON t1.t1Key = t2.t2Key
    WHEN MATCHED AND t2.marked = 1 THEN DELETE
    WHEN MATCHED AND t2.isNewStatus = 1 THEN UPDATE SET val = t2.newVal, status = t2.newStatus
    WHEN MATCHED THEN UPDATE SET val = t2.newVal
    WHEN NOT MATCHED THEN INSERT (val, status) VALUES (t2.newVal, t2.newStatus);

p-himik 2023-08-10T21:25:10.515779Z

Yeah, definitely non-standard. I don't think there's a built-in way to do something like that in an ergonomic way. But you can always extend HoneySQL by writing your own project-local custom formatter so that something like

{:merge-into [:t1 [:t2 [:= :t1/t1Key :t2/t2Key]]
              [[:and :matched [:= :t2/marked 1]] :delete]
              ...]}

becomes the string that you want.

seancorfield 2023-08-10T21:25:58.282219Z

MS SQL Server has it toohttps://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16

p-himik 2023-08-10T21:26:45.376149Z

Oh... what?! PG also has it!

seancorfield 2023-08-10T21:26:47.205479Z

Ah, it's ANSI Standard https://en.wikipedia.org/wiki/Merge_%28SQL%29

😮 1

p-himik 2023-08-10T21:27:01.817039Z

Ahhh, in PG it's only in version 15, the latest one.

seancorfield 2023-08-10T21:27:11.584089Z

@joshuam Create a GH issue and I'll add it into the core.

joshuamz 2023-08-10T21:27:26.679259Z

Wow! Yes, thank you!

seancorfield 2023-08-10T21:27:50.495439Z

I'm planning a big HoneySQL blitz soon to try to get through the backlog of issues. Esp. the temporal stuff since I will need that for XTDB 2.0 🙂

p-himik 2023-08-10T21:29:11.014449Z

> It was officially introduced in the https://en.wikipedia.org/wiki/SQL:2003 standard, and expanded in the https://en.wikipedia.org/wiki/SQL:2008 standard Wow... It's been there for 20 years. And when I needed something that INSERT ... ON CONFLICT ... couldn't handle, I had to write a terrible CTE or straight up split the thing into multiple expressions.

joshuamz 2023-08-10T21:29:59.673959Z

https://github.com/seancorfield/honeysql/issues/499

👍🏻 1

seancorfield 2023-08-10T21:30:18.232119Z

Thanks!

joshuamz 2023-08-10T21:30:35.999919Z

Thank you sir 🫡

Clojurians Log v2

honeysql