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

Started by Arup Rakshitalmost 12 years ago7 messagesgeneral

aruprakshit@rocketmail.com

almost 12 years ago

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

Raymond O'Donnell

rod@iol.ie

almost 12 years ago

In reply to: Arup Rakshit (#1)

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

On 02/06/2014 11:54, 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 ?

You can use the array "overlap" operator, something like this:

SELECT .... WHERE tags && ARRAY['apple', 'banana'];

See here:

http://www.postgresql.org/docs/9.3/static/functions-array.html

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

scrawford@pinpointresearch.com

almost 12 years ago

In reply to: Arup Rakshit (#1)

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

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

aruprakshit@rocketmail.com

almost 12 years ago

In reply to: Raymond O'Donnell (#2)

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

You can use the array "overlap" operator, something like this:

SELECT .... WHERE tags && ARRAY['apple', 'banana'];

See here:

http://www.postgresql.org/docs/9.3/static/functions-array.html

Ray.

Yes. It is the one I need really. It worked. Great DB it is. Lots of utility
methods.

--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.

--Brian Kernighan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

aruprakshit@rocketmail.com

almost 12 years ago

In reply to: Steve Crawford (#3)

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

aruprakshit@rocketmail.com

almost 12 years ago

In reply to: Arup Rakshit (#5)

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

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

aruprakshit@rocketmail.com

almost 12 years ago

In reply to: Arup Rakshit (#6)

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

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