Error when inserting through JDBC

Question

When I run the sql statement below through psql it works fine, but when I try to run the same query by building it with a preparedstatement, it fails.

INSERT INTO Hvbp 
  (provider_number, weighted_clinical_process, 
   weighted_patience_experience, total_performance_score, 
   coordinates, latitude, longitude, address, city, state, zip) 
VALUES 
('010092', 43.909090909091, 13.5, 57.409090909091, 
 'POINT(33.206201 -87.525480)', 33.206200613000476, 
 -87.52548020899968, '809 UNIVERSITY BOULEVARD EAST', 'TUSCALOOSA', 'AL', '');

The error I keep getting is

org.postgresql.util.PSQLException: ERROR: column "coordinates" is of type geography but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 203

The coordinates column is of type GEOGRAPHY(POINT)

Answer 1

I know this is an old problem, but I just spend most of the day debugging the same basic problem and finally found a fix

What you're trying to do, is provide the POINT using WKT and have the server automatically convert that into a Geometry.

And as you've found that works if you include the WKT inside the body of the SQL, but fails if you use a parameter on a prepared statement.

There are 3 options for fixing it:

1. Use st_GeographyFromText in your SQL like so:

   INSERT INTO Hvbp(coordinates) VALUES( st_GeographyFromText(?) ) 
   

   And then set your parameters as WKT with setString.
   Depending on the frameworks involved, that might not be possible for you.

2. Use setObject on the preparedStatement instead of setString. For example:

   ps.setObject(1, "POINT(33.206201 -87.525480)", java.sql.Types.OTHER )
   

3. Change your JDBC driver settings to send strings as unspecified type, and then the server will do type conversions for you. To do that you want to change your JDBC URL to something like

   "jdbc:postgresql:my_db?stringtype=unspecified"
   

Answer 2

@Tim - thank you for your help with a similar problem - I had to write ST_GeometryFromText into my database and the JDBC Driver threw a similar exception as @Hanks got.

For further reference and clarification for others - this is my result using Java with JDBC:

INSERT INTO streets.points ( point_id, the_geom ) 
        VALUES( ?, ST_GeomFromText( ? , 25832)  );

And the inserted Geometry-String looked like that:

POINT(33.206201 -87.525480)