Re: How can I select rows by comparing an array data type column with multiple values ?

Hi,

Suppose I have a table

 CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
);

Now I have a query -

select * from sal_emp where pay_by_quarter && some_var

Now some var some times is fully empty, then I am getting error as 

PG::UndefinedFunction: ERROR:  operator does not exist: text[] && integer[]
       LINE 1: ...ps".* FROM "reporting_groups"  WHERE (ARRAY[NULL] && workpla...

                                                                    ^

How to fix this ? The error is taken from production and the example I gave above is the same as per our current query in production.
 
Regards,
Arup Rakshit

On Monday, 2 June 2014 9:04 PM, Steve Crawford <scrawford(at)pinpointresearch(dot)com> wrote:

On 06/02/2014 03:54 AM, Arup Rakshit wrote:

Hi,
>
>
>Suppose, I have a table as below :-
>
>
>id |    title     |    content     |       tags
>----+--------------+----------------+-------------------
>  1 | sample post  | lorem ipsum    | {apple,orange}
>  2 | another post | fruits are bad | {apple,hamburger}
>  3 | third post   | foo bar            | { banana, lemon }
>  4 | third post   | foo baz           | { watermelon, lemon }
>
>
>Now I want to select all rows, for which tags will having either one or all value from the this array [apple,banana] ? how should I write the query using such a set ?
>
>
>output should select 1,2,3.
> 
>Regards,
>Arup Rakshit
Perhaps the && (overlaps) operator will work best for you.
Check out the available operators at:
http://www.postgresql.org/docs/current/static/functions-array.html

Cheers,
Steve