From: Harald Fuchs <hari.fuchs@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Window function trouble
Date: Fri, 11 Dec 2009 16:40:13 +0100
Message-ID: <pu7hstiko2.fsf@srv.protecting.net> (raw)
References: <pud42mahnr.fsf@srv.protecting.net>
<pu7hsuafcq.fsf@srv.protecting.net>
<25983.1260468559@sss.pgh.pa.us>
<puy6la8rw7.fsf@srv.protecting.net>
<28855.1260486487@sss.pgh.pa.us>
In article <28855.1260486487@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Harald Fuchs <hari.fuchs@gmail.com> writes:
>> That being said, I still think that PostgreSQL could do better - how
>> about naming expression columns so that they are distinct from column
>> names?
> Even though the rules we use are pretty arbitrary, I'm hesitant to make
> changes in them; it seems more likely to break existing applications
> than to do anyone any good.
Well, it would be far less confusing. Here's a self-contained example:
CREATE TABLE t1 (
id serial NOT NULL,
adr text NOT NULL,
usr text NOT NULL,
PRIMARY KEY (id)
);
COPY t1 (adr, usr) FROM stdin DELIMITER '|';
a1|u1_1
a2|u2_1
a2|u2_2
a2|u2_2
a3|u3_1
a3|u3_2
a3|u3_2
a3|u3_3
a3|u3_3
a3|u3_3
\.
SELECT CASE lag(adr) OVER (ORDER BY adr, usr)
WHEN adr THEN NULL
ELSE adr
END,
usr, count(*)
FROM t1
GROUP BY adr, usr
ORDER BY adr, usr;
The result set is:
adr | usr | count
-----+------+-------
a1 | u1_1 | 1
a2 | u2_1 | 1
a3 | u3_1 | 1
| u2_2 | 2
| u3_2 | 2
| u3_3 | 3
This means IMHO that "GROUP BY adr, usr" groups by the column named
"adr", whereas "ORDER BY adr, usr" orders by the unnamed CASE
expression which happens to "hide" the column name without warning.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: hari.fuchs@gmail.com
Subject: Re: Window function trouble
In-Reply-To: <pu7hstiko2.fsf@srv.protecting.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox