title: PostgreSQL use case when result in where clause
body: <p>I use complex CASE WHEN for selecting values. I would like to use this result in WHERE clause, but Postgres says column 'd' does not exists.</p>
  
  <pre><code>SELECT id, name, case when complex_with_subqueries_and_multiple_when END AS d
  FROM table t WHERE d IS NOT NULL
  LIMIT 100, OFFSET 100;
  </code></pre>
  
  <p>Then I thought I can use it like this:</p>
  
  <pre><code>select * from (
      SELECT id, name, case when complex_with_subqueries_and_multiple_when END AS d
      FROM table t 
      LIMIT 100, OFFSET 100) t
  WHERE d IS NOT NULL;
  </code></pre>
  
  <p>But now I am not getting a 100 rows as result. Probably (I am not sure) I could use LIMIT and OFFSET outside select case statement (where WHERE statement is), but I think (I am not sure why) this would be a performance hit.</p>
  
  <p>Case returns array or null. What is the best/fastest way to exclude some rows if result of case statement is null? I need 100 rows (or less if not exists - of course). I am using Postgres 9.4.</p>
  
  <p><strong>Edited:</strong></p>
  
  <pre><code>SELECT count(*) OVER() AS count, t.id, t.size, t.price, t.location, t.user_id, p.city, t.price_type, ht.value as houses_type_value, ST_X(t.coordinates) as x, ST_Y(t.coordinates) AS y, 
  CASE WHEN t.classification='public' THEN 
  ARRAY[(SELECT i.filename FROM table_images i WHERE i.table_id=t.id ORDER BY i.weight ASC LIMIT 1), t.description] 
  WHEN t.classification='protected' THEN 
  ARRAY[(SELECT i.filename FROM table_images i WHERE i.table_id=t.id ORDER BY i.weight ASC LIMIT 1), t.description]
  WHEN t.id IN (SELECT rl.table_id FROM table_private_list rl WHERE rl.owner_id=t.user_id AND rl.user_id=41026) THEN 
  ARRAY[(SELECT i.filename FROM table_images i WHERE i.table_id=t.id ORDER BY i.weight ASC LIMIT 1), t.description]
  ELSE null
  END AS main_image_description
  FROM table t LEFT JOIN table_modes m ON m.id = t.mode_id 
  LEFT JOIN table_types y ON y.id = t.type_id 
  LEFT JOIN post_codes p ON p.id = t.post_code_id 
  LEFT JOIN table_houses_types ht on ht.id = t.houses_type_id 
  WHERE datetime_sold IS NULL AND datetime_deleted IS NULL AND t.published=true AND coordinates IS NOT NULL AND coordinates &amp;&amp; ST_MakeEnvelope(17.831490030182, 44.404640972306, 12.151558389557, 47.837396630872) AND main_image_description IS NOT NULL
  GROUP BY t.id, m.value, y.value, p.city, ht.value ORDER BY t.id LIMIT 100 OFFSET 0
  </code></pre>