Postgres JDBC Numbered Parameters

Question

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?

Mark Rotteveel · Accepted Answer · 2014-11-15 12:06:07Z

2

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

answered Nov 15, 2014 at 12:06

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tstenner · Accepted Answer · 2023-08-03 10:14:17Z

0

CTEs can make Mark's solution a bit more readable by having all parameters in the first line (like a poor man's function definition) and accessing them by name in the query itself:

WITH vars AS (SELECT ? aColumnLabel)
SELECT
  mycolumn->>vars.aColumnLabel,
  count(*)
FROM
  mytable, vars
GROUP BY
  mycolumn->>vars.aColumnLabel

answered Aug 3, 2023 at 10:14

tstenner

10.4k10 gold badges61 silver badges94 bronze badges

Collectives™ on Stack Overflow

Postgres JDBC Numbered Parameters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related