... WHERE ['ID_two','ID_four'] COINTAINS_AT_LEAST_ONE_ID_FROM ['ID_one','ID_two','ID_three']

Question

I would like to have an SQL query where I check if one ID (of an array with ID's) matches at least one ID out of an array of other ID's. The ID's are stings.

It should be like:

SELECT * FROM table1 WHERE table1.ids COINTAINS_AT_LEAST_ONE_ID_FROM ['ID_one','ID_two','ID_three']

table1.ids contains also an array with IDs. For example, let's say ['ID_two','ID_four']

I saw several solutions but I am using an old PostgreSQL Version (8.0.2 on Redshift) and nothing worked so far.

Do you know a solution? :)

Greetings

Answer 1

Here is an ugly solution (if you're desperate), it should work on 8.0 :

First create the t10 table that you'll use as an iterator (adjust it to your maximum array size):

CREATE TABLE T10 (ID INTEGER);

INSERT INTO T10 VALUES (1);
INSERT INTO T10 VALUES (2);
INSERT INTO T10 VALUES (3);
INSERT INTO T10 VALUES (4);
INSERT INTO T10 VALUES (5);
INSERT INTO T10 VALUES (6);
INSERT INTO T10 VALUES (7);
INSERT INTO T10 VALUES (8);
INSERT INTO T10 VALUES (9);
INSERT INTO T10 VALUES (10);

Then here is the test table :

create table table1 (
    ids text[]
);

insert into table1 (ids) values (ARRAY['ID_two','ID_four']);

select * from table1;

       ids        
------------------
 {ID_two,ID_four}

Then, the query, that use T10 to iterate over all members of your arrays :

\set myids ARRAY['''ID_one''','''ID_two''','''ID_three''']

select *
  from (
     select ids[iter.pos] as ids
     from table1
     join (select id as pos from t10) iter
     on iter.pos <= array_length(ids,1)) as A
  join (
     select (:myids)[iter.pos] as ids
     from (select id as pos from t10) iter
     where iter.pos <= array_length(:myids,1)) as B
  on a.ids = b.ids;

  ids   |  ids
--------+--------
 ID_two | ID_two