postgres case expression with a where clause

Question

I want to filer data from my postgres database. i want to use where clause together with postgres case expressions for filtering data in a single query. i want some thing like this logic

select *from [my-table-name] where ( CASE 
     WHEN column1 = 0 THEN condition1 
     ELSE condition2
  END
)

what i want is if my column1 is zero i want to filter those rows with condition1 and if column1 is not zero i want to filter those rows with condition2. if case expression was not used i have to write query like this

select *from [my-table-name] where column1 = 0 and condition1
select *from [my-table-name] where column1 <> 0 and condition2

for getting those required data

i tried writing seperate queries for getting those required data

Hm, WHERE column1 = 0 and condtion1 or column1 <> 0 and condition2? Why do you need CASE instead of plain boolean logic? Are condition1 or condition2 computationally heavy? Anyway, here's the documentation link to working syntax: postgresql.org/docs/current/… — STerliakov
– STerliakov, Commented Jul 21, 2023 at 9:40
Also, you always have UNION to merge two queries into one, it may be more efficient in some circumstances compared to heavy CASE in WHERE clause. — STerliakov
– STerliakov, Commented Jul 21, 2023 at 9:41
no condition1 and condition2 was not computationally heavy. but if those column values are zero my filtering condition will become different. because i was filtering a date column on certain conditions. but on seeing your second comment about union, i think it will be a better solution. thanks for the help — hafis
– hafis, Commented Jul 21, 2023 at 9:52
(column1 = 0 AND condition1) OR (column1 <> 0 AND condition2) is not equivalent to (CASE WHEN column1 = 0 THEN condition1 ELSE condition2 END because the CASE statement will return the value of condition2 when column1 IS NULL but the pure logical expression will return NULL. — JohnH
– JohnH, Commented Jul 22, 2023 at 22:18

JohnH · Accepted Answer · 2023-07-23 17:50:02Z

Using CASE expressions in WHERE clauses can adversely affect query performance because doing so can prevent the execution planner from identifying opportunities to use available indexes. I'll use EXPLAIN (ANALYZE) to demonstrate the potential performance difference between using CASE expressions and pure conditional logic in the WHERE clause.

The following commands establish the demonstration environment:

CREATE TABLE case_tests (
  id serial PRIMARY KEY,
  column1 integer);

INSERT INTO case_tests (column1)
SELECT random() * 2
  FROM generate_series(1,1000000) s(n);

The first query uses CASE to determine which additional conditional test is applied:

SELECT *
  FROM case_tests
  WHERE CASE column1
          WHEN 0 THEN id <= 100
          ELSE id > 999900
        END;

Running this query with EXPLAIN (ANALYZE) returned the following:

Seq Scan on case_tests  (cost=0.00..21925.00 rows=500000 width=8) (actual time=0.014..87.607 rows=99 loops=1)
  Filter: CASE column1 WHEN 0 THEN (id <= 100) ELSE (id > 999900) END
  Rows Removed by Filter: 999901
Planning Time: 0.050 ms
Execution Time: 87.631 ms

The second query is functionally equivalent to the first, but uses pure conditional logic instead of a CASE expression:

SELECT *
  FROM case_tests
  WHERE    (column1 = 0 AND id <= 100)
        OR (column1 = 0 IS NOT TRUE AND id > 999900);

The resulting explain plan shows a total execution time about 1000 times faster than the query using CASE (0.088 ms vs. 87.631 ms):

Bitmap Heap Scan on case_tests  (cost=10.38..665.39 rows=95 width=8) (actual time=0.025..0.051 rows=99 loops=1)
  Recheck Cond: ((id <= 100) OR (id > 999900))
  Filter: (((column1 = 0) AND (id <= 100)) OR (((column1 = 0) IS NOT TRUE) AND (id > 999900)))
  Rows Removed by Filter: 101
  Heap Blocks: exact=2
  ->  BitmapOr  (cost=10.38..10.38 rows=197 width=0) (actual time=0.016..0.017 rows=0 loops=1)
        ->  Bitmap Index Scan on case_tests_pkey  (cost=0.00..5.22 rows=106 width=0) (actual time=0.006..0.006 rows=100 loops=1)
              Index Cond: (id <= 100)
        ->  Bitmap Index Scan on case_tests_pkey  (cost=0.00..5.11 rows=91 width=0) (actual time=0.010..0.010 rows=100 loops=1)
              Index Cond: (id > 999900)
Planning Time: 0.145 ms
Execution Time: 0.088 ms

Collectives™ on Stack Overflow

postgres case expression with a where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related