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

Postgresql – Use a string value from a CTE in an IN-clause in a simple SQL query

I am trying to make a simple SQL query in PostgreSQL to use in our team:

with findData as (
  select '(''XXX'',''YYYY'')' as dataList
)
select * from findData

That gives the result:

('XXX','YYYY')

Exactly what I wanted !

But I want to use it like this in a simple SQL script:

select t.variabel1,
       t.variabel2
from myTable t
where t.variabel3 in (select dataList from findData);

But this doesn’t work …

Has my problem a solution?

Tags:

2

Answers


  1. 0

    The datalist is treated as a single string value, currently. So, to use the ‘IN’, you need them seperate. Can be done like this.

    with findData as (
    select unnest(array['XXX', 'YYYY']) as dataList
)
select t.variabel1, t.variabel2
from myTable t
where t.variabel3 in (select dataList from findData);

    The unnest function is used to expand the array into rows.

    Login or Signup to reply.
  2. 0

    The simplest way is probably to use an array and use = ANY instead f IN:

    SELECT ...
FROM mytable AS t
WHERE t.variabel3 = ANY (ARRAY['XXX','YYYY']);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search