Clojurians Log v2

I am getting this weird error where when I try to format an insert query it adds an extra ( before the insert statement.

[(INSERT INTO users (first_name, last_name, email, status) VALUES (?, ?, ?, CAST(? AS user_status)) ON CONFLICT (email) DO UPDATE SET first_name = EXCLUDED.first_name, last_name = EXCLUDED.last_name, status = EXCLUDED.status) abcd@gmail.com_ abcd@gmail.com_ abcd@gmail.com_ unregistered]

["INSERT INTO users (first_name, last_name, email, status) VALUES (?, ?, ?, CAST(? AS user_status)) ON CONFLICT (email) DO UPDATE SET first_name = EXCLUDED.first_name, last_name = EXCLUDED.last_name, status = EXCLUDED.status"
 "abcd@gmail.com_"
 "abcd@gmail.com_"
 "abcd@gmail.com_"
 "unregistered"]

(-> (insert-into :users)
      (columns :first_name :last_name :email :status)
      (values [[first-name
                last-name
                email
                (sql/call :cast (or status "active")
                          :user_status)]])
      (psqlh/upsert (-> (psqlh/on-conflict :email)
                        (psqlh/do-update-set :first_name :last_name :status)))
      sql/format)

@danisam17 HoneySQL does not run queries -- it just builds the SQL -- so how you run the resulting SQL cannot possibly have any impact whatsoever on the syntax of the generated SQL.

There must be something else at play here. What is psqlh in your code above?

@seancorfield psqlh is the postgres helper for honeysql https://github.com/nilenso/honeysql-postgres

I suspected as much...

I'm trying your code locally and confirmed it produced the right SQL (as expected).

Yeah, when I directly call the function with the same data it returns the correct SQL. I tried it without the postgres helper and it still returns the query with the extra (

[(INSERT INTO users (first_name, last_name, email, status) VALUES (?, ?, ?, CAST(? AS user_status))) abcd@gmail.com_ abcd@gmail.com_ abcd@gmail.com_ unregistered]

Note what is missing in that.

You are doing something differently between the code that works and the code that does not.

You must require honeysql-postgres.format in order to enable the PG extensions. Without that, you won't get any of the PG clauses expanded -- HoneySQL won't know about them, so it'll ignore them.

user=> sql-data
{:insert-into :users, :columns (:first_name :last_name :email :status), :values [["Sean" "Corfield" "<mailto:sean@corfield.org|sean@corfield.org>" #sql/call [:cast "test" :user_status]]], :upsert {:on-conflict (:email), :do-update-set (:first_name :last_name :status)}}
user=&gt; (sql/format sql-data)
["INSERT INTO users (first_name, last_name, email, status) VALUES (?, ?, ?, CAST(? AS user_status)) " "Sean" "Corfield" "<mailto:sean@corfield.org|sean@corfield.org>" "test"]
user=&gt; (require '[honeysql-postgres.format :as ff])
nil
user=&gt; (sql/format sql-data)
["INSERT INTO users (first_name, last_name, email, status) VALUES (?, ?, ?, CAST(? AS user_status)) ON CONFLICT (email) DO UPDATE SET first_name = EXCLUDED.first_name, last_name = EXCLUDED.last_name, status = EXCLUDED.status" "Sean" "Corfield" "<mailto:sean@corfield.org|sean@corfield.org>" "test"]

But you have something else being done to your data structure in the case that produces the (incorrect) ( .. ) wrapping the SQL.

Hmm.. Not sure what I am missing. Will take a look again. Thanks for the help @seancorfield

@danisam17 Please let us know what you figure out.