Check if array column contains any value from a query array-CodePudding

I have a table with the column names VARCHAR(30) ARRAY NOT NULL and I want to query using a list of names, and return rows where the names column has values which are also in my query list.

names for a certain row will look like: {margret,susan,karen,daren}

Right now I can select using contains and one value

SELECT * FROM test_table WHERE names @> '{"john"}';

^Returns the rows where names array contains john. (As intended).

Everything I try querying using multiple values (ex: `john', 'doe', 'jane') results in errors... here is what I tried and the corresponding errors:

SELECT * FROM test_table WHERE names @> ANY('{"john", "jane"}');

^ERROR:  could not find array type for data type character varying[]

SELECT * FROM test_table WHERE names @> ANY('{"john", "jane"}'::varchar(40));

^ERROR:  op ANY/ALL (array) requires array on right side

CodePudding user response：

Will this work?

create table testarr(names varchar(10)[] );
insert into testarr values(array['margaret','susan','karen','daren']);
select * from testarr where names && '{"margaret","john"}';
select * from testarr where names && '{"mary","john"}';

Result:

postgres=# create table testarr(names varchar(10)[] );
CREATE TABLE
postgres=# insert into testarr values(array['margaret','susan','karen','daren']);
INSERT 0 1
postgres=# select * from testarr where names && '{"margaret","john"}';
            names
------------------------------
 {margaret,susan,karen,daren}
(1 row)

postgres=# select * from testarr where names && '{"mary","john"}';
 names
-------
(0 rows)