Question 214365 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – postgres case expression with a where clause

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

Tags:

2

Answers


  1. 0

    Using both the WHERE clause and the CASE expression in a single query, you can perform the desired filtering. Here is how to go about it:

        SELECT *
    FROM table
    WHERE 
      CASE
        WHEN column_01 = 0 THEN condition_01
        ELSE condition_02
    END;

    The CASE statement in this query checks the column1’s value. If it equals 0, condition 1 is evaluated. If it does not, condition 2 is evaluated. A true or false outcome from the CASE expression will be used as the filter condition in the WHERE clause.

    Hope this is helpful.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search