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?

user330315 · Accepted Answer · 2011-07-07 10:19:50Z

9

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.

answered Jul 7, 2011 at 10:19

user330315

Sign up to request clarification or add additional context in comments.

Comments

iconoclast · Accepted Answer · 2023-06-23 04:09:00Z

6

You can't use an alias (like virtual_field here) from the SELECT clause in the WHERE clause like that.

You can use them in ORDER BY but not in WHERE.

edited Jun 23, 2023 at 4:09

iconoclast

23k17 gold badges112 silver badges160 bronze badges

answered Jul 7, 2011 at 9:09

TomH

9,3003 gold badges34 silver badges30 bronze badges

1 Comment

dnch Over a year ago

Really? Oh, damn. So, I guess I need to use WHERE (field_a + field_b + field_c) > 0 then?

Collectives™ on Stack Overflow

PostgreSQL Virtual Column—column does not exist?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related