I have the following SQL query:
query_string = "SELECT sum(unmatched), " \
"TIMESTAMP WITH TIME ZONE 'epoch' + INTERVAL '1 second' * " \
"round(extract('epoch' from time_window) / {}) * {} as time_window " \
"FROM aggregate_counts WHERE reconciliation_name = %s " \
"GROUP BY round(extract('epoch' from time_window) / {})".format(interval_sec, interval_sec, interval_sec)
cur.execute(query_string, (reconciliation_name))
It works fine unless I want to avoid using string replacement for "interval_sec" and use positional parameters instead, like I have for other parameters. Problem is, if I do that:
query_string = "SELECT sum(unmatched), " \
"TIMESTAMP WITH TIME ZONE 'epoch' + INTERVAL '1 second' * " \
"round(extract('epoch' from time_window) / %s) * %s as time_window " \
"FROM aggregate_counts WHERE reconciliation_name = %s " \
"GROUP BY round(extract('epoch' from time_window) / %s)"
cur.execute(query_string, (interval_sec, interval_sec, reconciliation_name, interval_sec))
I get the following error:
Error handler middleware caught the following exception: {'S': 'ERROR', 'V': 'ERROR', 'C': '42803', 'M': 'column "aggregate_counts.time_window" must appear in the GROUP BY clause or be used in an aggregate function', 'P': '177', 'F': 'parse_agg.c', 'L': '1344', 'R': 'check_ungrouped_columns_walker'}
File File "pg8000/core.py", line 1829, in execute ps = cache['ps'][key] KeyError: ("SELECT sum(unmatched), TIMESTAMP WITH TIME ZONE 'epoch' + INTERVAL '1 second' * round(extract('epoch' from time_window) / %s) * %s as time_window FROM aggregate_counts WHERE reconciliation_name = %s GROUP BY round(extract('epoch' from time_window) / %s)", ((701, 1, ), (701, 1, ), (705, 0, .text_out at 0x10c58cea0>)))
Can positional parameters only be used in comparisons (=, >=, < etc...)?
So it's basically not possible to have this. Reason being the %s in select clause will get converted to let's say, $x positional argument, and the %s in group by will get converted to $y (x and y being the respective positions.) Now postgres has no way of knowing that after resolving, these two will be the same. Hence it assumes that "aggregate_counts.time_window" is not present in GROUP BY. I understand it's not the perfect explanation, but this is kind of what is happening.