Search Postgresql Archives

GROUP BY column alias?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Maybe I'm getting too old to SQL anymore, but I ran across something yesterday in a machine generated query that took me over an hour to figure out.  

Here's a little testcase.  Maybe somebody can explain why the last "Not Expected" case does what it does.

select version();
PostgreSQL 8.4.1 on i386-apple-darwin10.0.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5646), 64-bit

create table foo(day timestamp);
insert into foo values (now());
insert into foo values (now());

Expected:
select day, count(*) from foo group by day;
            day             | count 
----------------------------+-------
 2010-02-18 15:41:37.335357 |     1
 2010-02-18 15:41:39.471746 |     1
(2 rows)

Expected:
select day::date, count(*) from foo group by day;
    day     | count 
------------+-------
 2010-02-18 |     1
 2010-02-18 |     1
(2 rows)

Expected:
select day::date, count(*) from foo group by day::date;
    day     | count 
------------+-------
 2010-02-18 |     2
(1 row)

Expected:
select day::date as bar, count(*) from foo group by bar;
    bar     | count 
------------+-------
 2010-02-18 |     2
(1 row)

Not Expected:
select day::date as day, count(*) from foo group by day;
    day     | count 
------------+-------
 2010-02-18 |     1
 2010-02-18 |     1
(2 rows)

Note in the last case, the "day" column is aliased as "day", but the group by using the physical "day" column, not the alias.  That boggles my mind, especially when you consider the case above it, where "day" is aliased as "bar" and grouping by "bar" works as expected.

eric
-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux