Sql – PostgreSQL use case when result in where clause

casepostgresqlsql

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.

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;

Then I thought I can use it like this:

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;

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.

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.

Edited:

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 && 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

Best Answer

To use the CASE WHEN result in the WHERE clause you need to wrap it up in a subquery like you did, or in a view.

SELECT * FROM (
    SELECT id, name, CASE 
        WHEN name = 'foo' THEN true
        WHEN name = 'bar' THEN false
        ELSE NULL
        END AS c
    FROM case_in_where
) t WHERE c IS NOT NULL

With a table containing 1, 'foo', 2, 'bar', 3, 'baz' this will return records 1 & 2. I don't know how long this SQL Fiddle will persist, but here is an example: http://sqlfiddle.com/#!15/1d3b4/3 . Also see https://stackoverflow.com/a/7950920/101151

Your limit is returning less than 100 rows if those 100 rows starting at offset 100 contain records for which d evaluates to NULL. I don't know how to limit the subselect without including your limiting logic (your case statements) re-written to work inside the where clause.

WHERE ... AND (
    t.classification='public' OR t.classification='protected'
    OR t.id IN (SELECT rl.table_id ... rl.user_id=41026))

The way you write it will be different and it may be annoying to keep the CASE logic in sync with the WHERE limiting statements, but it would allow your limits to work only on matching data.

Related Topic