Thread: Window function trouble
I have a table like this: CREATE TABLE tbl ( host text NOT NULL, adr ip4 NOT NULL, usr text NOT NULL ); (ip4 is from the ip4r contrib module) and I want the number of entries per address and per user: SELECT adr, usr, count(*) FROM tbl WHERE host = ? AND adr <<= ? GROUP BY adr, usr ORDER BY adr, usr That's pretty basic stuff and returns something like this: adr1 usr1_1 cnt1_1 adr1 usr1_2 cnt1_2 adr1 usr1_3 cnt1_3 adr2 usr2_1 cnt2_1 ... But I want the address to be NULL if it's the same as the address of the previous row. I came up with this: SELECT CASE lag(adr) OVER (ORDER BY adr) WHEN adr THEN NULL ELSE adr END AS myaddr, usr, count(*)FROM tbl WHERE host = ? AND adr <<= ? GROUP BY adr, usr ORDER BY adr, usr This returns something like adr1 usr1_1 cnt1_1 NULL usr1_2 cnt1_2 NULL usr1_3 cnt1_3 adr2 usr2_1 cnt2_1 ... what's exactly what I want. But when I don't name the CASE expression (i.e. I delete "AS myaddr"), I get the following: adr1 usr1_1 cnt1_1 adr2 usr2_1 cnt2_1 ... The other users for one address are gone. Does anyone know why?
In article <pud42mahnr.fsf@srv.protecting.net>, Harald Fuchs <hari.fuchs@gmail.com> writes: > The other users for one address are gone. Does anyone know why? Update: they are not gone, but they've moved to the end of the result set. Apparently the CASE expression is named "adr" unless named otherwise, and the result set is sorted by this expression instead of the column named "adr". Does anyone know what the SQL standard says about that?
Harald Fuchs <hari.fuchs@gmail.com> writes: > Apparently the CASE expression is named "adr" unless named > otherwise, and the result set is sorted by this expression instead of > the column named "adr". Does anyone know what the SQL standard says > about that? The SQL standard says the default name for any output column other than a simple column reference is implementation-dependent. I think our implementation involves looking at the default value for a CASE. regards, tom lane
In article <25983.1260468559@sss.pgh.pa.us>, Tom Lane <tgl@sss.pgh.pa.us> writes: > The SQL standard says the default name for any output column other > than a simple column reference is implementation-dependent. I think > our implementation involves looking at the default value for a CASE. Thanks for the clarification, Tom - so it's not a PostgreSQL bug, but a misfeature of the SQL standard. That being said, I still think that PostgreSQL could do better - how about naming expression columns so that they are distinct from column names? The current implementation throws an error e.g. for SELECT adr, CASE lag(adr) OVER (ORDER BY adr) WHEN adr THEN NULL ELSE adr END, usr, count(*)FROM tbl WHERE host = 'h1' GROUP BY adr, usr ORDER BY adr, usr namely `ORDER BY "adr" is ambiguous' which I find somewhat confusing.
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. regards, tom lane
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.