'PostgreSQL asking for 'group by' clause in where, when sending parameters

I have a simple query in PostgreSQL which is ok when I run it without any query parameters : 

select date_trunc('week', action_time),count(*) from event 
       group by date_trunc('week', action_time);

But if I try to send the 'week' as a parameter like this (in Java): 

PreparedStatement statement = connection.prepareStatement
    ("select date_trunc(?, action_time),count(*) from event" 
    + " group by date_trunc(?, action_time)");
statement.setString(1,"week");
statement.setString(2,"week");
statement.execute();

it'll throw the following error: 

ERROR: column "event.action_time" must appear in the GROUP BY clause or 
be used in an aggregate function

is this normal behavior ?

sqlpostgresqljdbcgroup-byprepared-statement


Solution 1:[1]

When the query is prepared there's no guarantee that you will bind the same value ('week') for both placeholders. If you don't, the query would be illegal, and that's why postgres doesn't allow preparing it.

One way around this could be to change your query so you only bind 'week' once, and use it from inside a subquery:

PreparedStatement statement = connection.prepareStatement
    ("select dt, count(*) from (select date_trunc(?, action_time) as dt " 
    + "from event) s group by dt");
statement.setString(1,"week");
statement.execute();

Solution 2:[2]

I think this should work, but Postgres can be a bit finicky. For instance, the following does not work:

select date_trunc(val, now())
from (select 'week' as val) t

But this does:

select date_trunc(val, now())
from (select cast('week' as text) as val) t

You might check if this version works:

select date_trunc(datepart, action_time), count(*)
from event cross join
     (select cast(? as text) as datepart) vars
group by date_trunc(datepart, action_time);

And then supply only one parameter.

Solution 3:[3]

Like Mureinik mentioned its because postgres cant prove the statement arguments are the same.

I was able to use a column alias to provide the argument once.

eg

select date_trunc(?, action_time), count(*) from event 
       group by date_trunc(?, action_time);

becomes

 select date_trunc(?, action_time) as action_t, count(*) from event 
        group by action_t;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mureinik
Solution 2 Gordon Linoff
Solution 3 Stephen

Related Questions