Function call return value returned as aliased column not referenceable

Question

Can someone help me understand what's going on, here?

SELECT
    jsonb_array_length(to_jsonb("gi"."colors_distilled")) "gi_colors_distilled_jsonb_len"
FROM
    "general_inventories" "gi"
WHERE
    "gi_colors_distilled_jsonb_len" > 1;

Fails with:

-- ERROR:  column "gi_colors_distilled_jsonb_len" does not exist
-- LINE 6:     "gi_colors_distilled_jsonb_len" > 1;

This works (implementing the expression directly within the condition), however:

SELECT
    jsonb_array_length(to_jsonb("gi"."colors_distilled")) "gi_colors_distilled_jsonb_len"
FROM
    "general_inventories" "gi"
WHERE
    jsonb_array_length(to_jsonb("gi"."colors_distilled")) > 1;

It feels like I must be missing something obvious, but there's so little there that I must be botching an assumption about how/when aliases are evaluated.

Answer 1

The reason for this is the TRUE order of operations in an SQL statement. It's a bit counter-intuitive, but once you grok the whole Zeitgeist, it'll fall into place and you'll understand why certain constucts won't work even though a novice may think they should!

Order of operations:

From here (good explanation), it's:

• 1. FROM/JOIN
• 2. WHERE
• 3. GROUP BY
• 4. HAVING
• 5. SELECT
• 6. ORDER BY
• 7. LIMIT/OFFSET

So, we see that the WHERE comes before the SELECT, but it is in the SELECT where the aliases are defined, but the WHERE clause can't "see" the aliases because it's already been evaluated.

The ORDER BY clause on the other hand comes after the SELECT and therefore you can use aliases in that clause!

Your own question is answered within the link.

For example, a common mistake in SQL is incorrectly referencing column aliases.

Take a look here for another take - explains how MySQL sometimes ignores these and the problems it can lead to (STRICT_MODE being OFF - never run MySQL this way). The author (Lukas Eder) is always worth looking out for (YouTube for example).