Fork me on GitHub
#sql
<
2019-10-19
>
murtaza5216:10:06

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 ?

seancorfield17:10:14

How are you waiting the honeysql code @murtaza52?

murtaza5217:10:12

(-> (hsh/insert-into :sample)
      (hsh/values [{:location (format "ST_SetSRID(ST_MakePoint(%s,%s), 4326)" (first  location) (second location))}])
      hsc/format)
@seancorfield ^

murtaza5217:10:30

this string when executed directly using execute-one! works - ["INSERT INTO sample (location) VALUES (ST_SetSRID(ST_MakePoint(0.291056,32.621431), 4326))"]

seancorfield18:10:20

In that HoneySQL expression you are passing a string as the value of :location which is why it is failing.

seancorfield18:10:48

You need to provide function calls in the HoneySQL DSL...

seancorfield18:10:36

(! 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=> 

murtaza5218:10:45

@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.

seancorfield18:10:22

Use the functional version instead.

seancorfield18:10:29

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=> 

murtaza5218:10:48

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 ?

dharrigan18:10:04

I use honeysql with postgis too

dharrigan18:10:10

Here's an example of what I do

dharrigan18:10:16

(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 ")"))))

seancorfield18:10:02

I'm a bit surprised the insert generated from HoneySQL fails above. That must be a restriction in the PostGIS stuff?

dharrigan18:10:45

It could be that one has to cast the type to a geometry

seancorfield18:10:38

That error is saying the opposite -- it has a geometry value (and a bigint) but no matching function call?

dharrigan18:10:33

Maybe it's geography, I do this too:

dharrigan18:10:46

(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 ")"))))

dharrigan18:10:13

I know, by doing it this way, I have no issues 🙂

dharrigan18:10:50

I don' t use the sql/call function

murtaza5218:10:45

could it be the bigint casting is causing the problem, maybe the st_Srid is expecting geometry and int

dharrigan18:10:17

for my lat/lng, they are floats

dharrigan18:10:35

and WSG84 is an int

murtaza5218:10:08

yes for wsg84, the error identifies it as bigint

seancorfield18:10:54

https://postgis.net/docs/manual-2.5/ST_SetSRID.html shows it expects geometry and integer so that might be the issue.

seancorfield18:10:41

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)

seancorfield19:10:03

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=> 

seancorfield19:10:46

If all else fails, you could fallback to @dharrigan’s sql/raw approach I guess @murtaza52

murtaza5219:10:13

@seancorfield thanks a lot for guiding through the solution, the above worked. @dharrigan thanks your code helped me catch a bug in my code !

dharrigan20:10:09

Glad to help! 🙂

seancorfield19:10:56

Nice. I'll add that example to the README then!

murtaza5219:10:15

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.

seancorfield19:10:29

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]

seancorfield19:10:53

That's in the README now. There are several examples of sql/call and sql/raw in the README already @murtaza52

seancorfield19:10:07

Happy to update the README if you have suggested clarifications...

seancorfield19:10:42

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]

👍 1
dharrigan20:10:12

I think I'll redo my code to use the sql/call functions now