GeoTools
  1. GeoTools
  2. GEOT-1835

EPSG factory needs to handle primary keys as integers

    Details

    • Type: Bug Bug
    • Status: Open Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.4.4, 2.5-M2
    • Fix Version/s: 2.5.9
    • Component/s: referencing
    • Labels:
      None
    • Environment:
      PostgreSQL 8.3

      Description

      The EPSG factory connected to a PostgreSQL 8.2 database works as expected. But connecting to the same database content on PostgreSQL 8.3 doesn't work anymore. We get the following exception:

      Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying
      	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
      	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
      	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)
      	at org.geotools.referencing.factory.epsg.DirectEpsgFactory.createCoordinateReferenceSystem(DirectEpsgFactory.java:1847)
      
      HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
      

      This is because DirectEpsgFactory assigns a String parameter in a PreparedStatement for a column which is declared as integer in the database. Prior to PostgreSQL 8.3, the conversion was applied automatically. Starting with PostgreSQL 8.3, it is not anymore. From http://www.postgresql.org/docs/8.3/static/release-8-3.html:

      "Previously, if a non-character value was supplied to an operator or function that requires text input, it was automatically cast to text, for most (though not all) built-in data types. This no longer happens: an explicit cast to text is now required for all non-character-string types."

      The fix is to change the return value of the private toPrimaryKey(String) function from String to int and update the callers accordingly. In the main time, a workaround proposed by Mark Leslie is to redefine the integer -> text cast as implicit. See http://www.postgresql.org/docs/8.3/interactive/sql-createcast.html.

        Issue Links

          Activity

          Hide
          Martin Desruisseaux added a comment -
          Fixed on geotidy.
          Show
          Martin Desruisseaux added a comment - Fixed on geotidy.

            People

            • Assignee:
              Unassigned
              Reporter:
              Martin Desruisseaux
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated: