sql 2021-07-29 | Slack Archive

Hey all, need to upsert this key value pair

:key "1234567",
:value {:val-a "hello", :val-b ["ABC" "XYZ" "PQR"], :val-c "MNO", :val-d ["RST"]}

in postgres using honeysql where value should be in jsonb format and key being string

seancorfield05:07:59

There's no need to cross-post in both #honeysql and #sql -- tell us what you've tried and what didn't work and what actual question you have. I've already pointed you at the documentation -- have you read it? Does it not answer your question?

Adie06:07:14

seancorfield06:07:22

Right, so explain what you've tried and where the docs fall short for you.

Adie06:07:41

Can you share the link of the doc you are referring to?

seancorfield06:07:24

You're being lazy. Both the honeysql project and the next.jdbc project link to the documentation.

Adie06:07:20

Okay cool. Just checked

Adie06:07:53

Thank you

Adie11:07:01

Hey @U04V70XH6, I am using https://github.com/nilenso/honeysql-postgres#upsert for upserting this data in my postgres database. The table has this schema:

CREATE TABLE my_table (
                      key varchar(150) NOT NULL,
                      value jsonb,
                      PRIMARY KEY (key)
);

My clojure code looks like this:

(defn upsert!
  [table record constraint-name where-clause]
  (-> (insert-into table)
      (values [record])
      (where* where-clause)
      (psqlh/upsert (-> (psqlh/on-conflict-constraint constraint-name)
                        (psqlh/do-update-set :key
                                             :value)
                        ))
      sql/format)

But I am getting this error on calling the upsert function on the same key twice :

2021-07-30 19:42:30.656 UTC [2376] ERROR:  duplicate key value violates unique constraint "my_table_pkey"
2021-07-30 19:42:30.656 UTC [2376] DETAIL:  Key (key)=(8343847329) already exists.
2021-07-30 19:42:30.656 UTC [2376] STATEMENT:  INSERT INTO my_table (driver_id, tags) VALUES ($1, $2)

As per the doc the query in postgres should look like below :

["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *"
    5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]

nilenso/honeysql-postgres PostgreSQL extension for honeysql Website http://nilenso.com Stars 180 The following query works fine when executed directly in the psql shell.

INSERT INTO my_table (key, value) VALUES ('8343847329', '{"val-a": "hello", "val-b": ["ABC" "XYZ" "PQR"], "val-c": "MNO", "val-d": ["RST"]}') ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value RETURNING *;

I feel my clojure code is unable to create the appropriate DML for the expected psql query.

seancorfield17:07:39

You can only use the old 1.x version of HoneySQL with that nilenso library. If you use the 2.x version of HoneySQL, you don't need the nilenso library, but the HoneySQL syntax is slightly different. See https://cljdoc.org/d/com.github.seancorfield/honeysql/2.0.0-rc5/doc/getting-started/postgresql-support

seancorfield17:07:18

And please stop cross-posting the same question to both #honeysql and #sql!

👍 2

2021-07-29

Channels