Postgres JDBC driver: PSQLException: syntax error at or near RETURNING
java, jdbc, postgresql
Solution
This looks like a problem with sql2o. The comments on the bug report say:
Whe using PostgreSQL, all SELECT statements will fail with message: org.postgresql.util.PSQLException: ERROR: syntax error at or near "RETURNING"
Seems to be related to this issue
This has been fixed with version 1.1.2.
The fix requires the QuirkMode enum flag to be set to PostgreSQL when creating a new instance of sql2o. It changes default behaviour of queries to NOT fetch generated keys by default. When it is needed to fetch generated keys, the returnGeneratedKeys parameter in the generateQuery method should be set.
Since Sql2o 1.6.0, include the sql2o-postgres dependency and use `new PostgresQuirks()` instead of `QuirksMode`.
Problem
For some reason the JDBC PostgreSQL driver is adding: RETURNING * to the end of select statements. Why? Code: ``` protected static final String AUTH_QUERY = "SELECT \"SECRET\" FROM \"user\" WHERE \"NAME\" = :name"; String password = sql2o.open().createQuery(AUTH_QUERY).addParameter("name", username).executeScalar(String.class); ``` Exception: ``` org.postgresql.util.PSQLException: ERROR: syntax error at or near "RETURNING" Position: 47 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at org.sql2o.Query.executeScalar(Query.java:533) at org.sql2o.Query.executeScalar(Query.java:577) at org.sql2o.Query.executeScalar(Query.java:568) ``` Data source (JNDI): ``` <Configure id="wac" class="org.eclipse.jetty.webapp.WebAppContext"> <New id="mydb" class="org.eclipse.jetty.plus.jndi.Resource"> <Arg></Arg> <Arg>jdbc/mydb</Arg> <Arg> <New class="com.mchange.v2.c3p0.ComboPooledDataSource"> <Set name="driverClass">org.postgresql.Driver</Set> <Set name="jdbcUrl">jdbc:postgresql://localhost:5432/mydb</Set> <Set name="user">user</Set> <Set name="password">pass</Set> </New> </Arg> </New> </Configure> ``` PostgreSQL JDBC driver version ``` <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>9.3-1101-jdbc41</version> </dependency> ``` Packet capture https://postimg.cc/image/gbl2dq4zx/ ``` No. Time Source Destination Protocol Length Info 12 0.175636000 127.0.0.1 127.0.0.1 PGSQL 182 >P/B/D/E/S Frame 12: 182 bytes on wire (1456 bits), 182 bytes captured (1456 bits) on interface 0 PostgreSQL Type: Parse Length: 69 Statement: Query: SELECT "SECRET" FROM "user" WHERE "NAME" = $1 RETURNING * Parameters: 1 Type OID: 1043 ```