Thread: BUG #1651: static date field is not recognized automatically when group by is used

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.

"Peter van der Meer" <peter.vd.meer@yifan.net> writes:
> 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

The GROUP BY forces the system to choose a type for the grouping column
(else it cannot know what semantics GROUP BY is to have) and given the
lack of any decoration on the literal, it chooses text.  Sorry, but we
are unlikely to "fix" this.  We do not want the semantics of SELECT to
change just because the result will later be used as an INSERT source,
so it's not workable to make use of the INSERT's column types in
resolving the type of the literal.

            regards, tom lane