postgres match through two lists (list in list)

Question

I'd like to know if there is a way to check if there is at least one string in list a which matches in list b.

> select 1 IN (1,2);
 ?column? 
----------
 t
(1 row)

In the case above I'm only checking 1 value against a list.

But if I try as below I got error.

=> select (1, 3) IN (1,2);
ERROR:  operator does not exist: record = integer
LINE 1: select (1, 3) IN (1,2);
                      ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.


=> select ANY((1, 3) IN (1,2));
ERROR:  syntax error at or near "ANY"
LINE 1: select ANY((1, 3) IN (1,2));

How do I perform this query??

Thanks in advance

GMB · Accepted Answer · 2020-04-25T22:46:45.547

2

You could use arrays instead of lists, and the overlaps operator:

select array[1, 3] && array[1, 2];

Yields

true

If you are starting from comma-delimited strings, you can use string_to_array() first:

select string_to_array('1,3', ',') && string_to_array('1,2', ',')

edited Apr 25 '20 at 22:46

answered Apr 25 '20 at 22:41

GMB

216,147
25
84
135

thanks, and if one array must be splited before? `string_split("1,2", ",")` – Aureliano Guedes Apr 25 '20 at 22:44
@AurelianoGuedes: you can use `string_to_array()` to split to turn a delimited string to an array. See my edited answer. – GMB Apr 25 '20 at 22:47
this way? `select "1" IN string_to_array("1,2", ",");` I got an error – Aureliano Guedes Apr 25 '20 at 22:51
Sorry return to this question, but this is right? `select string_to_array('AAF38820.1,AAF38821.1', ',') && ARRAY(select product_accession FROM ftable LIMIT 10);` because I've got error. – Aureliano Guedes Apr 25 '20 at 23:59

postgres match through two lists (list in list)

1 Answers1