|
Home > Archive > PostgreSQL Bugs > May 2005 > BUG #1651: static date field is not recognized automatically when group by is used
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
BUG #1651: static date field is not recognized automatically when group by is used
|
|
| Peter van der Meer 2005-05-06, 8:24 pm |
|
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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
| |
| Tom Lane 2005-05-06, 8:24 pm |
| "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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
|
|
|
|
|