Select rows according to another table with a comma-separated list of items-CodePudding

Have a table test.

select b from test

b is a text column and contains Apartment,Residential

The other table is a parcel table with a classification column. I'd like to use test.b to select the right classifications in the parcels table.

select * from classi where classification in(select b from test)

this returns no rows

select * from classi where classification =any(select '{'||b||'}' from test)

same story with this one

I may make a function to loop through the b column but I'm trying to find an easier solution

Test case:

create table classi as
select 'Residential'::text as classification 
union
select 'Apartment'::text as classification 
union
select 'Commercial'::text as classification;

create table test as
select 'Apartment,Residential'::text as b;

CodePudding user response：

You need to first split b into an array and then get the rows. A couple of alternatives:

select * from nj.parcels p where classification = any(select unnest(string_to_array(b, ',')) from test)

select p.* from nj.parcels p 
INNER JOIN (select unnest(string_to_array(b, ',')) from test) t(classification) ON t.classification = p.classification;

Essential to both is the unnest surrounding string_to_array.

CodePudding user response：

You don't actually need to unnest the array:

SELECT p.*
FROM   classi p
JOIN   test t ON classification = ANY (string_to_array(b, ','));

db<>fiddle here

The problem is that = ANY takes a set or an array, and IN takes a set or a list, and your ambiguous attempts resulted in Postgres picking the wrong variant. My formulation makes Postgres expect an array as it should.

For a detailed explanation see:

Note that my query also works for multiple rows in table test. Your demo only shows a single row, which is a corner case for a table ...
But also note that multiple rows in test may produce (additional) duplicates. You'd have to fold duplicates or switch to a different query style to get de-duplicate. Like:

SELECT *
FROM   classi p
WHERE  EXISTS (
   SELECT FROM test t
   WHERE  p.classification = ANY (string_to_array(t.b, ','))
   );

This folds duplicate elements within a single test.b as well as across multiple test.b. EXISTS returns a single row from classi per definition.