Postgres Pro
Downloads
Home   >   mailing lists

Re: How can I select rows by comparing an array data type column with multiple values ? - Mailing list pgsql-general

From Arup Rakshit
Subject Re: How can I select rows by comparing an array data type column with multiple values ?
Date
Msg-id 1401866114.64439.YahooMailNeo@web193901.mail.sg3.yahoo.com
Whole thread Raw
In response to Re: How can I select rows by comparing an array data type column with multiple values ?  (Arup Rakshit <aruprakshit@rocketmail.com>)
List pgsql-general
Tree view
I just figured it out :-

I just figured it out :-

yelloday_development=# select id, workplace_ids from reporting_groups ;
 id | workplace_ids 
----+---------------
  2 | {}
  3 | {}
  1 | {}
  5 | {2}
  4 | {1}
(5 rows)

yelloday_development=# select id, workplace_ids from reporting_groups where workplace_ids && ARRAY[2,4];
 id | workplace_ids 
----+---------------
  5 | {2}
(1 row)

yelloday_development=# select id, workplace_ids from reporting_groups where workplace_ids && ARRAY[];
ERROR:  cannot determine type of empty array
LINE 1: ...ce_ids from reporting_groups where workplace_ids && ARRAY[];
                                                               ^
HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[].
yelloday_development=# select id, workplace_ids from reporting_groups where workplace_ids && ARRAY[]::integer[];
 id | workplace_ids 
----+---------------
(0 rows)
                                                    
yelloday_development=# select id, workplace_ids from reporting_groups where workplace_ids && ARRAY[1,4,5]::integer[];
 id | workplace_ids 
----+---------------
  4 | {1}
(1 row)

 
Regards,
Arup Rakshit


On Wednesday, 4 June 2014 12:05 PM, Arup Rakshit <aruprakshit@rocketmail.com> wrote:


Just to help you guys the error - here is the full error stack . I am using an ORM with Rails

2.1.0 :001 > ReportingGroup.where("ARRAY[?] && workplace_ids", Workplace.select(:id))
   (0.4ms)  SELECT COUNT("workplaces"."id") FROM "workplaces"
D, [2014-06-04T12:00:23.479024 #14429] DEBUG -- :    (0.4ms)  SELECT COUNT("workplaces"."id") FROM "workplaces"
  Workplace Load (0.2ms)  SELECT id FROM "workplaces"
D, [2014-06-04T12:00:23.479496 #14429] DEBUG -- :   Workplace Load (0.2ms)  SELECT id FROM "workplaces"
  ReportingGroup Load (0.9ms)  SELECT "reporting_groups".* FROM "reporting_groups" WHERE (ARRAY[1,2] && workplace_ids)
D, [2014-06-04T12:00:23.560772 #14429] DEBUG -- :   ReportingGroup Load (0.9ms)  SELECT "reporting_groups".* FROM "reporting_groups" WHERE (ARRAY[1,2] && workplace_ids)
 => #<ActiveRecord::Relation [#<ReportingGroup id: 5, created_at: "2014-06-03 12:06:40", updated_at: "2014-06-03 12:06:40", company_id: nil, name: "test123", owner_id: nil, logo_file_name: nil, logo_content_type: nil, logo_file_size: nil, logo_updated_at: nil, cover_image_file_name: nil, cover_image_content_type: nil, cover_image_file_size: nil, cover_image_updated_at: nil, team_ids: [], reporting_group_ids: [], workplace_ids: [2]>, #<ReportingGroup id: 4, created_at: "2014-06-02 11:55:03", updated_at: "2014-06-04 05:56:20", company_id: nil, name: "biz", owner_id: nil, logo_file_name: nil, logo_content_type: nil, logo_file_size: nil, logo_updated_at: nil, cover_image_file_name: nil, cover_image_content_type: nil, cover_image_file_size: nil, cover_image_updated_at: nil, team_ids: [1], reporting_group_ids: [], workplace_ids: [1]>]> 
2.1.0 :002 > ReportingGroup.where("ARRAY[?] && workplace_ids", [])
  ReportingGroup Load (0.6ms)  SELECT "reporting_groups".* FROM "reporting_groups" WHERE (ARRAY[NULL] && workplace_ids)
D, [2014-06-04T12:00:30.264431 #14429] DEBUG -- :   ReportingGroup Load (0.6ms)  SELECT "reporting_groups".* FROM "reporting_groups" WHERE (ARRAY[NULL] && workplace_ids)
PG::UndefinedFunction: ERROR:  operator does not exist: text[] && integer[]
LINE 1: ...ps".* FROM "reporting_groups"  WHERE (ARRAY[NULL] && workpla...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "reporting_groups".* FROM "reporting_groups"  WHERE (ARRAY[NULL] && workplace_ids)
E, [2014-06-04T12:00:30.264546 #14429] ERROR -- : PG::UndefinedFunction: ERROR:  operator does not exist: text[] && integer[]
LINE 1: ...ps".* FROM "reporting_groups"  WHERE (ARRAY[NULL] && workpla...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "reporting_groups".* FROM "reporting_groups"  WHERE (ARRAY[NULL] && workplace_ids)
ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  operator does not exist: text[] && integer[]
LINE 1: ...ps".* FROM "reporting_groups"  WHERE (ARRAY[NULL] && workpla...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "reporting_groups".* FROM "reporting_groups"  WHERE (ARRAY[NULL] && workplace_ids)

 
Regards,
Arup Rakshit


On Wednesday, 4 June 2014 11:55 AM, Arup Rakshit <aruprakshit@rocketmail.com> wrote:


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@pinpointresearch.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







pgsql-general by date:

Previous
From: Arup Rakshit
Date:
Subject: Re: How can I select rows by comparing an array data type column with multiple values ?
Next
From: Jason Newton
Date:
Subject: pg_largeobject high overhead