0

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.

1
  • Hi, and welcome to dba.se! Not relevant to the question, but don't need to quote your identifiers (table, column, alias names) in PostgreSQL - using unquoted camel_case is far more legible anyway! Why have you tagged the question with both SQL Server and PostgreSQL? Please delete the inapplicable one - looks like PostgreSQL syntax to me.
    – Vérace
    Mar 11 at 7:17

1 Answer 1

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
    1. WHERE
    1. GROUP BY
    1. HAVING
    1. SELECT
    1. ORDER BY
    1. 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).

Not the answer you're looking for? Browse other questions tagged or ask your own question.