Postgres Query Assistance Required

Question

I was wondering is there a better way to write this particular query

SELECT flights.id as flightid,
                flights.icao, 
                aircraft.reg_no AS regno,
                starttime,
                endtime,
                originloc.country_code as origincountry,
                originloc.name_en AS originloc,
                destloc.country_code as destcountry,
                destloc.name_en AS destloc,
                (select pings.call from pings where pings.call != 'null' and pings.flightid = flights.id limit 1) as flightcall
                 FROM flights LEFT JOIN localities_sys originloc ON originloc.id = flights.originloc LEFT JOIN localities_sys destloc
                 ON destloc.id = flights.destloc LEFT JOIN aircraft ON aircraft.icao = flights.icao

If I try to add a condition at the end such as

 WHERE flightcall = 'test'

I get an error stating that ERROR: column "flightcall" does not exist

Any help is greatly appreciated

Thanks Damien

Answer 1

Alises in where clause are not supported in PostgreSQL. You either have to use the nested query or use below query -

SELECT flights.id as flightid,
       flights.icao, 
       aircraft.reg_no AS regno,
       starttime,
       endtime,
       originloc.country_code as origincountry,
       originloc.name_en AS originloc,
       destloc.country_code as destcountry,
       destloc.name_en AS destloc,
       (select pings.call
        from pings
        where pings.call != 'null'
        and pings.flightid = flights.id
        limit 1) as flightcall
FROM flights
LEFT JOIN localities_sys originloc ON originloc.id = flights.originloc
LEFT JOIN localities_sys destloc ON destloc.id = flights.destloc
LEFT JOIN aircraft ON aircraft.icao = flights.icao
WHERE pings.call = 'test';