Search code examples
sqlpostgresqlpostgisnpgsql

PostGIS query failing due to escape string?

I have this Postgres/PostGIS query:

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'm scratching my head because PostGIS doesn't have a 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.

Solution

  • The error results from unescaped single quotes in the 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 fixes the string literal, but you have more errors.
    Like @Paul hinted in a comment, ST_GeomFromText() expects the geometry WKT POINT(0 0). The explicit cast to float8 makes it look like you are trying to enter the Postgres function point() (had me confused at first). Simplify to:

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

    Note also, how I use dollar quoting in the second example to avoid having to escape single quotes altogether. As there aren't any single quotes left in your string literal after fixing the syntax, you might as well use single quotes again. Your parametrized query:

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

    You can add a cast to geometry to make it clear, like @Paul advises in his comment. But it works without explicit cast, too.