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 <[email protected]>
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 <[email protected]>
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 <[email protected]>
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
