I have a table like this:

mTable:

  |id     | text[] mVals        |  

  |1      | {"a,b"}             |
  |2      | {"a"}               |        
  |3      | {"b"}               |

I'd like a query to return both rows {a,b},{b} If I specify only b, but, it doesn't really return the row having atleast one of the specified values and only returns rows with the value specified.

I have tried this:

SELECT mVals
FROM mTable 
WHERE ARRAY['a'] && columnarray;  -- Returns only {'a'}  

Also tried:

SELECT mVals 
FROM mTable 
WHERE mVals && '{"a"}'; -- Returns only {'a'}  

Nothing seems to be working as it should. What could be the issue?

CodePudding user response：

to me it looks is working as expected, recreating your case with

create table test(id int, mvals text[]);
insert into test values(1, '{a}');
insert into test values(2, '{a,b}');
insert into test values(3, '{b}');

A query similar to the 1st one you posted works

SELECT mVals
FROM test 
WHERE ARRAY['a'] && mvals;

Results

 mvals
-------
 {a}
 {a,b}
(2 rows)

and with b instead of a

 mvals
-------
 {a,b}
 {b}
(2 rows)

P.S.: you should probably use the contain operator @> to check if a value (or an array) is contained in another array