PostgreSQL Virtual Column—column does not exist?

Question

Using PostgreSQL 8.4, I'm trying to put together the following query:

SELECT (field_a + field_b + field_c) AS virtual_field, *
FROM "entities" 
WHERE ("entities".thing_id = 9999 AND (virtual_field > 0)) 
AND (boolean_field = 't') 
ORDER BY virtual_field DESC

Unfortunately, I keep getting the following error:

PGError: ERROR:  column "virtual_field" does not exist
LINE 1: ...ies" ("entities".thing_id = 9999 AND (virtual_fiel...
                                                 ^

The error message is quite obvious, but I'll be damned if I can figure out the correct syntax for what I'm trying to do. field_a, field_b and field_c are all real columns in my entities table.

For reference, I'm using Rails (2.3.11) to compose the query. Here's the (anonymised) code I'm using:

Thing.entities.boolean_scope.find(:all, 
  :select     => "(field_a + field_b + field_c) AS virtual_field, *",
  :conditions => ['virtual_field > ?', value],
  :order      => 'virtual_field DESC'
)

My brain has failed me—can anyone help me figure it out?

Answer 1

If you put the "main" statement into a derived table, you can use the alias:

    SELECT *
    FROM 
    (
        SELECT (field_a + field_b + field_c) AS virtual_field,
               entities.*
        FROM entities 
    ) t
    WHERE thing_id = 9999 
      AND virtual_field > 0
      AND boolean_field = 't'
    ORDER BY virtual_field DESC

Btw: you don't need the expressioni boolean_field = 't' (if that is really a column of type boolean). AND boolean_field is enough as that is a valid boolean expression.