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

Postgresql – Usage of 'in' operator in Postgres sql

I have created a CTE using ‘with’ clause and I am using case when expression to populate a column in CTE and I wanted to use the result of case when expression in where condition as a criteria value to fetch the results using ‘in’ operator.

When there are values such as Range1 or Range2 which are matching the column values of table the query is producing proper result. I wanted to fetch results based on combination of values such as Range1,Range2 or Range3,Range4 using in operator in the where clause of postgreSQL. I have tried creating below case when and it does not give me results.

with sampleq as(
select case when trim(to_char(current_date,'Day')) like 'Monday' then '''Range1'',''Range2'',''Range3'''
        when trim(to_char(current_date,'Day')) like 'Tuesday' then '''Range4'',''Range5,''Range6'''
        when trim(to_char(current_date,'Day')) like 'Wednesday' then '''Range7'',''Range8,''Range9'''
        when trim(to_char(current_date,'Day')) like 'Thursday' then '''Range10'',''Range11'''
        when trim(to_char(current_date,'Day')) like 'Friday' then '''Range12'',''Range13'''
        else ' ' end as Rangecriteria
  ,Column1,column2 from sampletable

)select * from sampleq where column2 in (Rangecriteria)

Usually, if I use strings that are seperated by comma ‘in operator’ works fine. But if I use column to populate the value based on condition and use it ‘in operator’ it does not work. Could you please help me finding a solution here?

Thanks in advance,
S Ch Avinash

Tags:

2

Answers


  1. 0

    Your approach doesn’t work because you’re essentially expecting PostgreSQL to treat strings as code. How could PostgreSQL know whether '1, 2' means you want to find rows with numbers 1 and 2 or literal 1, 2 text?

    The most obvious solution is to have a master table with all these range definitions. If you don’t want a table, you can also emulate it with a CTE:

    WITH range_definition (weekday, "range") AS (
    SELECT 'Monday', 'Range1'
    UNION ALL SELECT 'Monday', 'Range2'
    UNION ALL SELECT 'Monday', 'Range3'
    UNION ALL SELECT 'Tuesday', 'Range6'
    -- ...
), DATA (weekday, column2) AS (
    SELECT 'Monday', 123
    UNION ALL SELECT 'Tuesday', 456
)
SELECT *
FROM DATA 
INNER JOIN range_definition ON DATA.weekday = range_definition.weekday;

    Then, you can use regular queries with regular IN operator.

    Login or Signup to reply.
  2. 0

    Rather than creating a list of strings then using the in operator create an array or strings and = Any. (See demo). Also see documentation for extract() function and array construction

    with the_range (rangecriteria) as
     ( select case the_day 
              when 1 then array['Range1','Range2','Range3']
              when 2 then array['Range4','Range5','Range6']
              when 3 then array['Range7','Range8','Range9']
              when 4 then array['Range10','Range11']
              when 5 then array['Range12','Range13']
              else array['']
              end
         from (select extract(isodow from current_date) the_day 
                where extract(isodow from current_date) < 6
              )
      )
select * 
  from sampletable  s
  join the_range    r
    on s.column2 = any (r.rangecriteria);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search