This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2022-01-31
Channels
- # announcements (1)
- # aws (4)
- # babashka (40)
- # beginners (89)
- # calva (13)
- # cider (3)
- # clj-kondo (36)
- # cljdoc (16)
- # clojure (74)
- # clojure-boston (1)
- # clojure-dev (7)
- # clojure-europe (30)
- # clojure-new-zealand (1)
- # clojure-nl (17)
- # clojure-uk (5)
- # clojurescript (16)
- # core-async (9)
- # cursive (16)
- # datahike (3)
- # datalog (6)
- # datascript (7)
- # datomic (15)
- # emacs (38)
- # events (2)
- # figwheel-main (3)
- # fulcro (6)
- # google-cloud (18)
- # graalvm (6)
- # gratitude (1)
- # honeysql (1)
- # introduce-yourself (1)
- # jobs (1)
- # leiningen (5)
- # lsp (6)
- # malli (11)
- # meander (2)
- # off-topic (4)
- # re-frame (6)
- # reitit (8)
- # releases (2)
- # remote-jobs (3)
- # reveal (4)
- # shadow-cljs (200)
- # sql (8)
- # tools-deps (16)
-- 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.@errelinaaron in your insert query - :order_date
can be changed to :order_date::date
to cast the string to a date type
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'
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?
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
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
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!