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

Question

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;

please always provide a [mre] please read https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — nbk, Aug 31 '22 at 14:41

Erwin Brandstetter · Answer 1 · 2022-08-31T20:25:30.683

You don't actually need to unnest the array:

SELECT c.*
FROM   classi c
JOIN   test   t ON c.classification = ANY (string_to_array(t.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 c.*
FROM   classi c
WHERE  EXISTS (
   SELECT FROM test t
   WHERE  c.classification = ANY (string_to_array(t.b, ','))
   );

This prevents duplication from elements within a single test.b, as well as from across multiple test.b. EXISTS returns a single row from classi per definition.
The most efficient query style depends on the complete picture.

score 0 · Accepted Answer · answered Aug 31 '22 at 15:02

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.

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

2 Answers2