Hacker News Reader

Comment by danso | Hacker News Reader

This seems as good of place as any to ask novice-level questions about PostgreSQL. I teach students SQLite and one of the most massive pain points in transitioning to PgSQL is how the latter, in the `WHERE` clause, fails to recognize aliases in the `SELECT` clause, e.g.

   SELECT UPPER(name) AS bigname
   FROM people
   WHERE bigname = 'JOE';

https://stackoverflow.com/questions/38040631/postgresql-does...

Apparently this is the SQL standard, which PgSQL seems to do a better job of following than SQLite, but I'm at a loss to understand why this computation is particularly problematic for PgSQL (or any variant) to adopt? Unlike other SQL standard rules that PgSQL follows that SQLite/MySQL doesn't (such as forbidding the selection of column names that aren't being GROUPed by in a GROUP BY clause), this strictness seems to be all inconvenience.

View on HN

MSSQL has the same (very frustrating) limitation. The obvious and ugly solution would be to wrap in a subquery (bonus: still std sql compliant, and performs well):

    SELECT bigname
    FROM (
        SELECT UPPER(name) AS bigname
        FROM people
    ) x
    WHERE bigname = 'JOE'

Add columns as necessary, it should work fine.

But this method can make large queries with lots of aliasing very difficult to read and lead to deep nesting. There is an alternative in MSSQL and (via a quick search) pgsql as well to keep the crazy nesting to a minimum:

    -- MSSQL
    SELECT bigname
    FROM people
    CROSS APPLY (SELECT UPPER(name) as bigname) x
    WHERE bigname = 'JOE'

    -- PGSQL (not 100% sure of syntax)
    SELECT bigname
    FROM people
    LEFT JOIN LATERAL (SELECT UPPER(name) as bigname) x on true
    WHERE bigname = 'JOE'

The problem is this might have a negative impact on performance because it mucks with the query planner. I've seen everything from huge, complex, CROSS APPLYs have literally zero performance penalty to completely benign single-column renames tanking query performance 10x, so beware if you use this.

It's because the WHERE clause is evaluated before the SELECT. The reason that SQLite allows you to do that is because SQLite is much, much simpler than PgSQL, MySQL and SQL Server (all of which don't allow you to do this).

http://tinman.cs.gsu.edu/~raj/sql/node22.html

Yes. I wish SQL select-statements put the select-clause last:

   from people
   where name = 'Joe'
   select upper(name) as bigname

1. It would mirror the other clauses (insert, update, and delete) which all begin with the table name.

2. It would be easier on my head. I skip the select-clause anyway when reading SQL, then come back up to it at the end, when I know the columns' source.

3. It would show that using column aliases in the where-clause would fail (unless the database did some kind of two-pass processing).

You just described LINQ

You would think that an initial pass of SELECT could be made only to note any aliases. It would be a much appreciated nod to usability.

That seems straightforward enough, don't know why I assumed SQLite's implementation of it was a feature as opposed to a consequence of its overall simplified model. I think I assumed/hoped that aliasing worked as a kind of function definition which WHERE could magically understand and evaluate.

As a counter point teradata (a ridiculously expensive closed source but fairly complex database) supports this feature. So it is definitely possible for this feature to be supported.

in SQLSERVER you could build your selects (using unique names or aliases) and then wrap it all in a common table expression like so:

; with cte as ( select a as A, b as B, c as C from dbo.table1 tbl1 join dbo.table2 tbl2 on tbl2.id = tbl1.id) select A, B, C from cte

I’m always saddened when I see this feature lacking in so many databases. Especially as Teradata supports this feature so i get to use it at my day job but not on any open source projects.

Reply on news.ycombinator.com