0

I have this query in PostgreSQL:

SELECT cars.*,
       manufacturers.company_name AS manufacturer_company_name,
       companies.mailing_name AS company_mailing_name,
       (SELECT car_documents.file_url FROM car_documents
         WHERE car_documents.car_id = cars.id AND car_documents.document_type = 1 LIMIT 1) AS ins_file_url,
       (SELECT car_documents.file_url FROM car_documents
         WHERE car_documents.car_id = cars.id AND car_documents.document_type = 3 LIMIT 1) AS guar_file_url,
       FROM "cars" 
       INNER JOIN "companies" ON "companies"."id" = "cars"."company_id" 
       LEFT JOIN shippers ON shippers.id = cars.shipper_id 
       WHERE (cars.status IS NULL
                AND cars.delivered < '2021-01-14'
                AND ins_file_url IS NOT NULL)

Why cannot I use the alias column ins_file_url in the WHERE clause? Is there any workaround for this behavior?

Thank you in advance

user984621
  • 46,344
  • 73
  • 224
  • 412
  • Just imagine you have the same column in some table and an alias with that name. How should the DBMS know, what is the column? Okay, with subquery you can use table alias like `tableAlias.columnName`, but what should you do for locally calculated column (that is in `select` list)? – astentx Jan 21 '21 at 09:08

2 Answers2

1

Just use your entire query in sub-query and apply where condition on sub-query as follows:

Select * from
(SELECT cars.*,
       manufacturers.company_name AS manufacturer_company_name,
       companies.mailing_name AS company_mailing_name,
       (SELECT car_documents.file_url FROM car_documents
         WHERE car_documents.car_id = cars.id AND car_documents.document_type = 1 LIMIT 1) AS ins_file_url,
       (SELECT car_documents.file_url FROM car_documents
         WHERE car_documents.car_id = cars.id AND car_documents.document_type = 3 LIMIT 1) AS guar_file_url,
       FROM "cars" 
       INNER JOIN "companies" ON "companies"."id" = "cars"."company_id" 
       LEFT JOIN shippers ON shippers.id = cars.shipper_id
       WHERE cars.status IS NULL
         AND cars.delivered < '2021-01-14' ) t
   Where ins_file_url IS NOT NULL
Popeye
  • 35,427
  • 4
  • 10
  • 31
1

Try to use double quotes around your alias names:

SELECT cars.*,
       manufacturers.company_name AS manufacturer_company_name,
       companies.mailing_name AS company_mailing_name,
       (SELECT car_documents.file_url FROM car_documents
         WHERE car_documents.car_id = cars.id AND car_documents.document_type = 1 LIMIT 1) AS ins_file_url,
       (SELECT car_documents.file_url FROM car_documents
         WHERE car_documents.car_id = cars.id AND car_documents.document_type = 3 LIMIT 1) AS guar_file_url,
       FROM "cars" 
       INNER JOIN "companies" ON "companies"."id" = "cars"."company_id" 
       LEFT JOIN shippers ON shippers.id = cars.shipper_id 
       WHERE (cars.status IS NULL
                AND cars.delivered < '2021-01-14'
                AND "ins_file_url" IS NOT NULL)
BlackRose01
  • 83
  • 5