HugSQL Error: column "date_answer" is of type date but expression is of type character varying?

Question

I am using clojure hugSQL to insert data into PostgreSQL database. I am trying to insert multiple rows into an answer table using :tuple* parameter. When passing a date I get the following error:

Error: column "date_answer" is of type date but expression is of type character varying?

The sample SQL query created by HugSQL:

INSERT INTO answer (a, b, c, d, date_answer) VALUES (62,76,NULL,NULL,'2020-05-13')

The same query works fine when inserted using terminal, so the format of a string seems to be fine. Is there any way to specify individual fields inside the :tuple* parameter so that I can do something like :date_answer::date

This is my HugSQL query:

INSERT INTO answer (a, b, c, d, date_answer) VALUES :tuple*:answers

score 2 · Answer 1 · answered May 13 '20 at 11:23

You have to parse the value passed to a date. E.g.,

(ns your-ns
  ...
  (:import
   (java.time LocalDate)
   (java.time.format DateTimeFormatter DateTimeParseException)))

(def ^:private yyyy-MM-dd-formatter
  (DateTimeFormatter/ofPattern "yyyy-MM-dd"))

;; Assuming your HugSQL function is called insert
(insert {:answers [[a b c d (LocalDate/parse date-answer yyyy-MM-dd-formatter)]]

Then your date-answer is of type "date", and not of "character varying".

HugSQL Error: column "date_answer" is of type date but expression is of type character varying?

1 Answers1