Fork me on GitHub
#honeysql
<
2021-07-31
>
Adie11:07:55

Hey all, 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"]

Adie11:07:49

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.

indy12:07:49

You can start debugging by comparing the output of the upsert query returned by your function with the one you intend to run. What is the output of the upsert query that you generated using honesql-postgres?

indy12:07:43

From the log that you posted, doesn't seem like the on conflict bit has been included

Adie13:07:28

can you please share some doc how to log querys generated from honeysql

indy13:07:43

You just have to call the function that generates the query from the REPL, in your case the upsert! function. It'll give you the output. sql/format will return something like ["SELECT * FROM some_table"].

indy13:07:20

From your past messages in other channels, it seems you've misunderstood honeysql. To clarify things, honeysql and it's postgres fork by nilenso are only query builders, they will only help you build queries using Clojure's datastructures and don't interact with the database at all. It is the JDBC driver that will interact with the DB, this is done using next.jdbc and the postgres driver in your case. So the flow is something like this, 1. You build SQL queries using honeysql that take clojure data structures as input. 2. You pass this query to next.jdbc's functions like next.jdbc/execute , next.jdbc.sql/query and others. 3. next.jdbc will interact with the DB using the chosen driver, postgres in your case and return you the results if any.

Adie14:07:47

Those are old messages when I had barely used honeysql

Adie15:07:29

I ran the exact same example on my repl as shown here https://github.com/nilenso/honeysql-postgres#upsert but my query is coming as different

Adie15:07:19

The exact same is happening for my table as well.

indy15:07:10

Are you using honeysql 2? It seems honeysql 2 doesn't work with honeysql-postgres https://github.com/nilenso/honeysql-postgres/issues/62

indy15:07:35

Not sure if the situation has changed since the comments are dated