This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2019-10-19
Channels
- # announcements (2)
- # beginners (12)
- # cider (12)
- # cljsrn (1)
- # clojure (56)
- # clojure-italy (1)
- # clojure-sweden (2)
- # clojure-uk (3)
- # clojured (6)
- # clojurescript (16)
- # cursive (1)
- # data-science (3)
- # datomic (8)
- # emacs (18)
- # figwheel-main (1)
- # graphql (5)
- # jobs (10)
- # luminus (4)
- # nrepl (27)
- # off-topic (5)
- # pathom (7)
- # shadow-cljs (66)
- # sql (40)
- # tools-deps (1)
- # vim (6)
I have the postgis extension installed for postgres, and I am trying to insert a geometry point in a table. The below query works from psql -
insert into sample(location) values(ST_SetSRID(ST_MakePoint(0.291266, 32.621238), 4326));
The below sql is generated from honeysql -
["INSERT INTO sample (location) VALUES (?)" "ST_SetSRID(ST_MakePoint(0.291056,32.621431), 4326)"]
However the above throws an error when I execute it (using next.jdbc
) -
ERROR: column "location" is of type geography but expression is of type
character varying Hint: You will need to rewrite or cast the expression.
Any pointers on how I should write the query ?How are you waiting the honeysql code @murtaza52?
(-> (hsh/insert-into :sample)
(hsh/values [{:location (format "ST_SetSRID(ST_MakePoint(%s,%s), 4326)" (first location) (second location))}])
hsc/format)
@seancorfield ^this string when executed directly using execute-one!
works - ["INSERT INTO sample (location) VALUES (ST_SetSRID(ST_MakePoint(0.291056,32.621431), 4326))"]
In that HoneySQL expression you are passing a string as the value of :location
which is why it is failing.
You need to provide function calls in the HoneySQL DSL...
(! 747)-> clj -Sdeps '{:deps {honeysql {:mvn/version "RELEASE"}}}'
Clojure 1.10.1
user=> (require '[honeysql.helpers :refer :all] '[honeysql.core :as h])
WARNING: update already refers to: #'clojure.core/update in namespace: user, being replaced by: #'honeysql.helpers/update
nil
user=> (require 'honeysql.types)
nil
user=> (-> (insert-into :sample)
(values [{:location #sql/call [:ST_SetSRID #sql/call [:ST_MakePoint 0.291 32.621] 4326]}])
(h/format))
["INSERT INTO sample (location) VALUES (ST_SetSRID(ST_MakePoint(?, ?), ?))" 0.291 32.621 4326]
user=>
@seancorfield how do I pass values into a reader macro -
{ :location #sql/call [:ST_SetSRID #sql/call [:ST_MakePoint lat lon] 4326]}
, lat
and lon
are values that I want to set in the fn. However the generated sql has the symbol names instead of the values.
Use the functional version instead.
user=> (require '[honeysql.types :as sql])
nil
user=> (def location [0.291 32.621])
#'user/location
user=> (-> (insert-into :sample)
(values [{:location (sql/call :ST_SetSRID (sql/call :ST_MakePoint 0.291 32.621) 4326)}])
(h/format))
["INSERT INTO sample (location) VALUES (ST_SetSRID(ST_MakePoint(?, ?), ?))" 0.291 32.621 4326]
user=>
This one written manually as a string works -
["INSERT INTO sample (location) VALUES (ST_SetSRID(ST_MakePoint(0.291056,32.621431), 4326))"]
This one generated from honeysql fails -
["INSERT INTO sample (location) VALUES (ST_SetSRID(ST_MakePoint(?, ?), ?))"
0.291056
32.621431
4326]
error
ERROR: function st_setsrid(geometry, bigint) does not exist
Any ideas what is different in both the strings ?(defn location->st-point-sql
[location]
(let [{:keys [lat lng]} location]
;; intentional, lng is the first parameter
(sql/raw (str "ST_SetSRID(ST_POINT(" lng "," lat "), " WGS84 ")"))))
I'm a bit surprised the insert generated from HoneySQL fails above. That must be a restriction in the PostGIS stuff?
That error is saying the opposite -- it has a geometry
value (and a bigint
) but no matching function call?
(defn location->dwithin-sql
[location locations-config]
(let [{:keys [lat lng]} location
{:keys [radius]} locations-config]
(sql/raw (str "ST_DWithin(location, st_point(" lng ", " lat ")::geography, " radius ")"))))
could it be the bigint
casting is causing the problem, maybe the st_Srid
is expecting geometry
and int
https://postgis.net/docs/manual-2.5/ST_SetSRID.html shows it expects geometry
and integer
so that might be the issue.
Try this ["INSERT INTO sample (location) VALUES (ST_SetSRID(ST_MakePoint(?, ?), CAST(? AS integer)))" 0.291 32.621 4326]
-- I want to see if the default bigint
type is the problem here (although I'm a bit surprised that was the type it ended up with)
If that does work, here's how I made it:
user=> (-> (insert-into :sample)
(values [{:location (sql/call :ST_SetSRID (sql/call :ST_MakePoint 0.291 32.621) (sql/call :cast 4326 :integer))}])
(h/format))
["INSERT INTO sample (location) VALUES (ST_SetSRID(ST_MakePoint(?, ?), CAST(? AS integer)))" 0.291 32.621 4326]
user=>
If all else fails, you could fallback to @dharrigan’s sql/raw
approach I guess @murtaza52
@seancorfield thanks a lot for guiding through the solution, the above worked. @dharrigan thanks your code helped me catch a bug in my code !
Nice. I'll add that example to the README then!
yup that will serve as a nice example for sql/call too, maybe I missed the existing example. Also an example for sql/raw will be cool, I had been searching for a way to generate raw sql for DDL, and in the end just wrote some fns to do it, didnt realize sql/raw was there.
A common example in the wild is the PostGIS extension to PostgreSQL where you have a lot of function calls needed in code:
(-> (insert-into :sample)
(values [{:location (sql/call :ST_SetSRID
(sql/call :ST_MakePoint 0.291 32.621)
(sql/call :cast 4326 :integer))}])
(sql/format))
=> [#sql/regularize
"INSERT INTO sample (location)
VALUES (ST_SetSRID(ST_MakePoint(?, ?), CAST(? AS integer)))"
0.291 32.621 4326]
That's in the README now. There are several examples of sql/call
and sql/raw
in the README already @murtaza52
Happy to update the README if you have suggested clarifications...
Example: Raw SQL fragments that are strings are treated exactly as-is when rendered into the formatted SQL string (with no parsing or parameterization). Inline values will not be lifted out as parameters, so they end up in the SQL string as-is. Raw SQL can also be supplied as a vector of strings and values. Strings are rendered as-is into the formatted SQL string. Non-strings are lifted as parameters. If you need a string parameter lifted, you must use #sql/param or the param helper.
(-> (select :*)
(from :foo)
(where [:< :expired_at (sql/raw ["now() - '" 5 " seconds'"])])
(sql/format {:foo 5}))
=> ["SELECT * FROM foo WHERE expired_at < now() - '? seconds'" 5]
(-> (select :*)
(from :foo)
(where [:< :expired_at (sql/raw ["now() - '" #sql/param :t " seconds'"])])
(sql/format {:t 5}))
=> ["SELECT * FROM foo WHERE expired_at < now() - '? seconds'" 5]
Awesome @seancorfield!