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

Use a column of arrays as a condition in Postgresql

I want to use a column of arrays as a condition in postgres. The condition is applied on a column composed of text value.

This is the column made out of arrays (after be transformed with string_to_array.

enter image description here

And this is the tax_role column.

enter image description here

I’tried a mix of ANY and IN but I get this error message.

Here’s my script :

select
*
from tax_role
where tax_role_2021.address_num IN ANY
      (SELECT string_to_array(corporate_data.address_num, '-') FROM corporate_data 
       where lower(corporate_data.name) LIKE '%some_name%'); -- this is the query that return the arrays

But it doesn’t seem to work.

Tags:

2

Answers


  1. 0

    The error you’re encountering is due to the misuse of the IN and ANY clauses together.

    In PostgreSQL, when you’re comparing against an array, you can use the ANY keyword, but it’s not used in conjunction with IN. Instead, you can use it like this:

    Try this modified script:

    SELECT *
FROM tax_role
WHERE tax_role_2021.address_num = ANY
      (SELECT string_to_array(corporate_data.address_num, '-') 
       FROM corporate_data 
       WHERE lower(corporate_data.name) LIKE '%some_name%');

    This script will compare each address_num in tax_role_2021 against the arrays returned by the subquery. If there’s a match, the row from tax_role will be included in the result set.

    Login or Signup to reply.
  2. 0

    The problem is a little unclear when considering the use of IN or ANY. It depends on your use case.

    If you want to check that an address number is in a specific set you should use the IN keyword, or if you want to match with any of its occurrences, you should opt for the ANY keyword. But do not use both at the same time.

    Your subquery to convert a string to an array seems perfect so there is no issue in that.

    I believe that in your case, the keyword IN will be used, try modifying Joaquin’s query with IN keyword. It should work then.

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

Back To Top
Search