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

Question

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 ?

Answer 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();