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

Arup Rakshit <aruprakshit@xxxxxxxxxxxxxx> · Wed, 4 Jun 2014 14:25:44 +0800 (SGT)

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@xxxxxxxxxxxxxxxxxxxx> 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