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

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

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;
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search