PostgreSQL RETURNING clause column names are always quoted

[Bruno Leite]

Hi,

There seems to be a problem retrieving auto-generated keys (using the RETURNING clause) on PostgreSQL. By default PostgreSQL auto-converts all identifiers (including table and column names) to lower case. When QueryDSL is generating the SQL query, in particular the RETURNING clause, it fetches the column metadata, extracts the column name and sends it, with the rest of the statement, to the PostgreSQL driver. The problem is that QueryDSL always stores the column name in uppercase and the PostgreSQL driver always escapes the RETURNING clause parameters.

Example of column metadata from auto-generated Q types:

  public void addMetadata() {
    ...
    addMetadata(id, ColumnMetadata.named("ID").withIndex(1).ofType(Types.BIGINT).withSize(19).notNull());
    ...
  }

And the PostgreSQL driver code responsible for escaping the parameters (PgStatement.java):

  static String addReturning(BaseConnection connection, String sql, String columns[],
      boolean escape) throws SQLException {
    if (!connection.haveMinimumServerVersion(ServerVersion.v8_2)) {
      throw new PSQLException(
          GT.tr("Returning autogenerated keys is only supported for 8.2 and later servers."),
          PSQLState.NOT_IMPLEMENTED);
    }


    sql = sql.trim();
    if (sql.endsWith(";")) {
      sql = sql.substring(0, sql.length() - 1);
    }


    StringBuilder sb = new StringBuilder(sql);
    sb.append(" RETURNING ");
    for (int i = 0; i < columns.length; i++) {
      if (i != 0) {
        sb.append(", ");
      }
      // If given user provided column names, quote and escape them.
      // This isn't likely to be popular as it enforces case sensitivity,
      // but it does match up with our handling of things like
      // DatabaseMetaData.getColumns and is necessary for the same
      // reasons.
      if (escape) {
        Utils.escapeIdentifier(sb, columns[i]);
      } else {
        sb.append(columns[i]);
      }
    }


    return sb.toString();
  }

Note that the "escape" argument is always true and enforced by the PostgreSQL driver itself (PgConnection.java):

  public PreparedStatement prepareStatement(String sql, String columnNames[]) throws SQLException {
    if (columnNames != null && columnNames.length != 0) {
      sql = PgStatement.addReturning(this, sql, columnNames, true);
    }
    ...

The resulting SQL query:

insert into SOME_TABLE (ID, COLUMN_1 COLUMN_2) values (nextval('seq_id'), 'value_1', 'value_2') RETURNING "ID";

If you noticed, only the RETURNING clause column name is being escaped, which will result on an exception like org.postgresql.util.PSQLException: ERROR: column "ID" does not exist.

This issue is also described here: https://www.postgresql.org/message-id/56bd3f97-c3e5-4ac8-daf9-ed6e73f48014%40list-group.com

Is there any "easy" workaround for this issue without having to quote every column name in the DB schema (and force the uppercase of all columns)?

BTW, I'm using querydsl-sql module for code generation.

Thank!