Postgres JDBC Numbered Parameters

I have a table in a Postgres 9.3 database with a json column like this:

CREATE mytable (
  mycolumn json
)

I would like to execute queries from a Java application that look like this:

SELECT
  mycolumn->>'somefield',
  count(*)
FROM
  mytable
GROUP BY
  mycolumn->>'somefield'

When I try to use a PreparedStatement like this:

SELECT
  mycolumn->>?,
  count(*)
FROM
  mytable
GROUP BY
  mycolumn->>?

I get the following error:

PSQLException: ERROR: column "mytable.mycolumn" must appear in the GROUP BY clause or be used in an aggregate function

It makes sense that this happens because Postgres cannot guarantee that the two positional parameters are the same.

Using psql, I can prepare a statement like this:

PREPARE mystatement AS
  SELECT
    mycolumn->>$1,
    count(*)
  FROM
    mytable
  GROUP BY
    mycolumn->>$1

Is it possible to do this with JDBC?

Solution

No, this isn't possible. JDBC only has positional parameters, and therefor the PostgreSQL driver will render it as:

PREPARE mystatement AS
  SELECT
    mycolumn->>$1,
    count(*)
  FROM
    mytable
  GROUP BY
    mycolumn->>$2

And as the value of $1 is not necessarily the same as $2, the parser of PostgreSQL will reject it as you are potentially not grouping on the same column.

The solution might be to do:

SELECT a.aColumnLabel, count(*)
FROM (
    SELECT mycolumn->>? as aColumnLabel
    FROM mytable
) a
GROUP BY a.aColumnLabel