Trying to filter null values in aliased column – postgresql?

have the following postgresql command:

select 
id, page -> 'link' as link, login_time
from 
my_table 
limit 200

I would like to add in a where clause to filter out where the link value is not null – so it contains a value.

when i run the following i get an error message:

select 
id, page -> 'link' as link, login_time
from 
my_table 
where link is not null
limit 200

column link not found

any ideas on how to solve this problem?

Answer

In a SQL SELECT query, the logical order of operations is not the lexical order.

Syntactically, the order is:

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

But the logical order (ignoring certain edge-cases and things like windowing functions and RDBMS-specific column aliasing) is:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

As the SELECT happens after the WHERE (and HAVING) you need to put predicates that depend on your projections (your SELECT clause) into an outer-query.

SELECT
    *
FROM
    (
        SELECT
            id,
            page -> 'link' AS link,
            login_time
        FROM
            my_table
    ) AS iq
WHERE
    iq.link IS NOT NULL
LIMIT
    200

Alternatively, you can still do the test in the WHERE clause, it just means having to repeat yourself:

SELECT
    id,
    page -> 'link' AS link,
    login_time
FROM
    my_table
WHERE
    ( page -> 'link' ) IS NOT NULL
LIMIT
    200

(Personal rant: SQL’s DRY-violating syntax has been a personal peeve of mine for at least a decade now – I don’t understand how it’s been 44 years since SQL was standardized in 1975, yet none of the major SQL vendors have made moves to modernize the syntax to be less asinine. MySQL did introduce some weird things like special GROUP BY behaviour and aliasing, but since version 8 they’ve decided to become more conformant instead of pushing for changes for the sake of programmer ergonomics – so until a vendor gets serious about modernizing SQL we’ll be stuck with a shortsighted and arbitrary aesthetic decision made before computers even had displays capable of displaying more than 40 lines at a time).