Fork me on GitHub
#sql
<
2022-01-31
>
erre lin13:01:10

-- Using HugSQL and next.jdbc. SQLs are as follows:

-- :name create-rush-orders-table
-- :command :execute
-- :result :raw
-- :doc Create the rush orders table
CREATE TABLE rush_orders (
  id                  SERIAL PRIMARY KEY,
  order_num           VARCHAR(14),
  vendor              VARCHAR(40),
  order_date          DATE NOT NULL DEFAULT CURRENT_DATE, -- order sent date
  arrival_date        DATE NOT NULL DEFAULT CURRENT_DATE, -- order arrival date
  lib_note            VARCHAR(100),
  record_created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)

-- :name add-rush-order
-- :command :insert
-- :result :one
-- :doc Insert one record into the table "orders"
INSERT INTO rush_orders (order_num, order_date, arrival_date, lib_note)
            VALUES (:order_num, :order_date, :arrival_date, :lib_note);
I want to call sth like (add-rush-order db {:order_num "xxx" :order_date "2022-01-01"}), but got error saying the date is a string, not a date type. I also tried 2022-01-01 without the double quotations, but clearly the error became Invalid number: 2022-01-01. How could I, in this case, insert a date correctly into a table? Thank you. Update: Ahh I see Clojure.jave-time and its doc makes sense to me. I'll go through it first.

lukasz14:01:20

@errelinaaron in your insert query - :order_date can be changed to :order_date::date to cast the string to a date type

erre lin17:01:01

AHHH this is so sweet! Thank you for the hint:+1:

lukasz17:01:08

You'll need it more often to help to get the right data types out as well - it's a nifty feature, for example you can pass relative time queries like this order_date >= current_timestamp - :time_ago::interval where time_ago can be a string of '5 months'

erre lin18:01:06

Thank you lukasz! 👍 This is really helpful, as I didn't find any of such suggestions when reading HugSQL doc or Luminus web (https://luminusweb.com/docs/database.html). Could you let me know where can I find more such tips on SQL with Clojure?

lukasz18:01:13

type casing syntax is PG specific (.as in select * from orders where ordered_at = '2021-01-01'::date ) I'm not familiar with Luminus, but HugSQL docs are really comprehensive https://www.hugsql.org and it looks like they just got redesigned

👍 1
lukasz18:01:12

One thing you might find a bit of out of date that Hugsql docs refer to clojure.java.jdbc a lot, but you should be using the next.jdbc adapter

erre lin18:01:57

You're right! I'm pretty new to Clojure. When I found HugSQL, next.jdbc already seems to be the most recommended way. I use the adapter suggested by HugSQL: https://www.hugsql.org/hugsql-adapters/setting-an-adapter I'll need to read these docs (PG and HugSQL) more carefully so as to not miss the tips you showed to me. Appreciate your help. 👍 Awesome!

🙌 1