6 ms·
This is a pretty trivial list. Useful for beginners I guess. I seriously take issue with "Reference Column Position in GROUP BY and ORDER BY" though. If it is
by tempguy9999 7y ago
This is a pretty trivial list. Useful for beginners I guess.
I seriously take issue with "Reference Column Position in GROUP BY and ORDER BY" though. If it is restricted to ad-hoc (AKA messing-about) queries I'd be fine with it, but it won't be. Just don't do it.
- wfriesen 7y agoIt's especially egregious in the ORDER BY, since there you have the option of using column aliases.
- tempguy9999 7y agoI always, always forgot what column aliases I can use where. Thanks for the reminder.
- commandlinefan 7y agoAre you saying you can't use column aliases in group by? What version of Postgres are you using? I just tried it in 11.5 and it worked: # select cust_id as c, sum(avail_balance) as b from account group by c order by b;
- tempguy9999 7y agoInteresting. It doesn't work in MSSQL, and I understand that's correct (ie. isn't allowed) per the standard.
- commandlinefan 7y agoHuh - I guess I never thought about it. It makes sense to disallow it, though - column aliases are there to rename complex expressions, which you probably _shouldn't_ be grouping on anyway.
- tempguy9999 7y agoI think it's for other reasons (and grouping on expressions is quite reasonable anyway). It's (IIRC!) something to do with the situation of select x + y as x from ... group by x which x are we talking about? (Logically that example is crap because only the alias x makes sense, but something like that anyway).
- yellowapple 7y ago> which you probably _shouldn't_ be grouping on anyway. This is frequently unavoidable, though. Or more precisely: it could be avoided with a sane database design, but the databases on which I have to work for my day job are the precise opposite of "well-designed", so grouping on complex expressions is unfortunately an inevitability.
- commandlinefan 7y agoThat's true - I can definitely imagine having to group on something like "concat(lastname + ', ' + firstname)".
- wfriesen 7y agoI was thinking of Oracle, where aliases are evaluated at column protection time, so after grouping but before ordering.
- irrational 7y agoIt's useful for people new to Postgres, since many of these things are particular to Postgres.
- tempguy9999 7y agoNot really. Most is pretty well standard SQL (CTE optimisation fence pre PG12 being one exception, and there are a couple more, but really it's mostly standard stuff).
- barrkel 7y agoThe :: syntax for CAST() is also a psql-ism.
- tejtm 7y agoMaybe that is what it is now, but I still have muscle memory using it in Informix.
- irrational 7y agoMaybe. I come from the Oracle world and the majority of these don't apply to Oracle.