Hacker News new | ask | show | jobs
by tempguy9999 2406 days 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.

2 comments

It's especially egregious in the ORDER BY, since there you have the option of using column aliases.
Are 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;
I was thinking of Oracle, where aliases are evaluated at column protection time, so after grouping but before ordering.
Interesting. It doesn't work in MSSQL, and I understand that's correct (ie. isn't allowed) per the standard.
Huh - 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.
> 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.

That's true - I can definitely imagine having to group on something like "concat(lastname + ', ' + firstname)".
I 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).
I always, always forgot what column aliases I can use where. Thanks for the reminder.
It's useful for people new to Postgres, since many of these things are particular to Postgres.
Not 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).
The :: syntax for CAST() is also a psql-ism.
Maybe that is what it is now, but I still have muscle memory using it in Informix.
Maybe. I come from the Oracle world and the majority of these don't apply to Oracle.