PostGIS error due to escape line?

Question

I have this Postgres / PostGIS request:

UPDATE raw.geocoding SET the_geom = ST_Transform(ST_GeomFromText('POINT(((E'-96.6864379495382')::float8) ((E'32.792527154088')::float8))', 4326),3081) WHERE id=((10793455)::int4) 

When I run it, I get this error:

 ERROR: syntax error at or near "')::float8) ((E'" LINE 2: ...sform(ST_GeomFromText('POINT(((E'-96.6864379495382')::float8... ^ ********** Error ********** ERROR: syntax error at or near "')::float8) ((E'" SQL state: 42601 Character: 94 

I scratch my head because PostGIS has no problem with escaped data ( for example ), and the query was generated from npgsql based on this parameterized query:

 UPDATE raw.geocoding SET the_geom = ST_Transform(ST_GeomFromText('POINT(:longitude :latitude)', 4326),3081) WHERE id=:id 

I am running Postgres 9.1.5 and PostGIS 2.0.1.

Answer 1

An error occurs from uninsulated single quotes in a string. The standard way is to double them:

 UPDATE raw.geocoding SET the_geom = ST_Transform(ST_GeomFromText( 'POINT(((E '' -96.6864379495382 '' )::float8) ((E '' 32.792527154088 '' )::float8))', 4326),3081) WHERE id=((10793455)::int4) 

This captures the string literal, but you have more errors.
As @Paul hinted in a comment, ST_GeomFromText() expects WKT POINT(0 0) geometry . The explicit cast to float8 pretends that you are trying to introduce the Postgres point() function (at first I was confused). Simplify:

 UPDATE raw.geocoding SET the_geom = ST_Transform(ST_GeomFromText( $$POINT(96.6864379495382 32.792527154088)$$ , 4326), 3081) WHERE id = 10793455 

Also note how in the second example I use dollar quotation to avoid having to avoid single quotes at all. Since there are no quotes left in the string literal after correcting the syntax, you can also use single quotes. Your parameterized query:

 UPDATE raw.geocoding SET the_geom = ST_Transform(ST_GeomFromText( $$POINT(:longitude :latitude)$$::geometry, 4326), 3081) WHERE id = :id 

You can add translation to geometry to make it understandable, as @Paul advises in your comment. But it works without an explicit cast.