The following bug has been logged online:
Bug reference: 1651
Logged by: Peter van der Meer
Email address: peter.vd.meer@yifan.net
PostgreSQL version: 7.4.7-6
Operating system: Debian Linux (Unstable)
Description: static date field is not recognized automatically when
group by is used
Details:
This is the table I used for testing:
CREATE TABLE testthis(id INT PRIMARY KEY, somedate DATE);
This query works as expected:
INSERT INTO testthis(id, somedate) SELECT 1 as id, '2005-02-02' as
somedate;
This query doesn't work:
INSERT INTO testthis(id, somedate) SELECT 2 as id, '2005-02-02' as
somedate GROUP BY id, somedate;
Executing it reports:
ERROR: column "somedate" is of type date but expression is of type text
A possible workaround is explicit typecasting:
INSERT INTO testthis(id, somedate) SELECT 2 as id, '2005-02-02'::Date as
somedate GROUP BY id, somedate;
(This works as expected again.)
The example SELECT in the INSERT INTO query here doesn't need GROUP BY, but
I'm sure one could think of a SELECT in a INSERT INTO query where a date is
given static and GROUP BY is neccecary for calculating an aggregrate-result.
