2025-02-18 honeysql | Clojure Slack Archive

honeysql

itaied 2025-02-18T11:45:16.346889Z

hey all, trying out honeySQL for XTDB how can i write the following

user=> INSERT INTO people (_id, name, info)
SELECT _id, name, info
  FROM people FOR ALL SYSTEM_TIME
  WHERE _id = 6
  ORDER BY _system_to DESC
  LIMIT 1;
INSERT 0 0
user=> SELECT * FROM people;
 _id |                                             info                                              |                likes                | name
-----+-----------------------------------------------------------------------------------------------+-------------------------------------+------
   6 | {"contact":[{"loc":"home","tel":"123"},{"loc":"work","registered":"2024-01-01","tel":"456"}]} |                                     | fred
   9 |                                                                                               | ["fishing",3.14,{"nested":"data"}] | bob
(2 rows)

I have tried something like

(execute! conn {:insert-into :test-01
                  :select [:*]
                  :from [[:test-01 :for :system-time :all]]
                  :order-by [[:_system_to :desc]]
                  :limit 1})

which producted the following

[SELECT * INSERT INTO test_01 FROM test_01 FOR SYSTEM_TIME ALL ORDER BY _system_to DESC LIMIT ? 1]

how can i write it using records, without the need to specify the fields of the entity?

refset 2025-02-18T12:21:25.556409Z

hey @itai the output from HoneySQL will need to look more like:

INSERT INTO docs (SELECT *
  FROM docs FOR ALL SYSTEM_TIME
  WHERE _id = 1
  ORDER BY _system_to DESC
  LIMIT 1)

https://play.xtdb.com/?version=2.0.0-beta6&type=sql-v2&enc=2&txs=NobwRAzgnhAuCmBbAtLAlo%2BYBcA7ArgDaEA0YsAHhDmAJIByAygKIBKAKgAQPsDynAEwD2AYwicAFAH00AkpxFDCARgCUnAGoBBADIBVZo0nL5AcgBmQoadUBuADq4GLDt3p9Bo8a2YBhXqwAIkYgMgLYnCYKSsoRpgBGAIYATqbyioQATNimnEkAXqYAvrZgRSTgAI748MlQNLDJNWBklNTYdExsXDz8wmKSLDp%2BXABUjpycAGKsvACyngNTAZy6OpyMAJqM7MxzUuy0c8wTnADqABJszJxhnAC8kacBgWycAEKbt9BwSFKwQk4r0YvlOOiOtC4ajKFXIVBoEHghHgIlgnFGnHMySEiEWEFKZB%2BCBQ6EwOAIxCKAF0gA

itaied 2025-02-18T12:35:11.871339Z

do you know how can i achieve it? what's the HoneySQL syntax?

p-himik 2025-02-18T13:19:36.397829Z

(sql/format {:insert-into [[:docs {:select   :*
                                   :from     [[:docs :for :all :system-time]]
                                   :where    [:= :-id 1]
                                   :order-by [[:-system-to :desc]]
                                   :limit    1}]]})
=> ["INSERT INTO DOCS (SELECT * FROM docs FOR ALL SYSTEM TIME WHERE _id = ? ORDER BY _system_to DESC LIMIT ?)" 1 1]

🙏 1

p-himik 2025-02-18T13:20:00.582229Z

Not sure about the nesting of :docs part since it results in DOCS.

p-himik 2025-02-18T13:25:01.884159Z

Ah, since parentheses don't seem to be needed, just remove the extra [...] around :docs.

itaied 2025-02-18T13:27:46.820179Z

great thanks, it worked

➕ 1

Clojurians Log v2

honeysql