Why does postgres tell me column 'airline' doesn't exist when I know it does?-CodePudding

I'm trying this simple modification on a query from a PostgreSQL course. The original works fine and is as follows:

SELECT  fl_date
        ,mkt_carrier AS airline
        ,mkt_carrier_fl_num AS flight
        ,origin
        ,dest
  FROM  performance
 WHERE  dest = 'ORD'

result

Now instead of destination, I'm asking to only show a certain airline:

SELECT  fl_date
        ,mkt_carrier AS airline
        ,mkt_carrier_fl_num AS flight
        ,origin
        ,dest
  FROM  performance
 WHERE  airline = 'UA'

..but instead of giving a result it spits back the following:

ERROR:  column "airline" does not exist
LINE 7:  WHERE  airline = 'UA'
                ^
SQL state: 42703
Character: 145

Clearly this column does exist; I see it in the initial result set. What am I missing?

CodePudding user response：

Because you can't use column alias name in WHERE or GROUP BY. You have to use real reference name in WHERE

SELECT  fl_date
        ,mkt_carrier AS airline
        ,mkt_carrier_fl_num AS flight
        ,origin
        ,dest
  FROM  performance
 WHERE  mkt_carrier = 'UA'

CodePudding user response：

You can not use where, group by, ... clause alias. Use real column names

SELECT  fl_date
        ,mkt_carrier AS airline
        ,mkt_carrier_fl_num AS flight
        ,origin
        ,dest
  FROM  performance
 WHERE  mkt_carrier = 'UA'