Home > mailing lists

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

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

From

Arup Rakshit

Date:

02 June 2014, 13:55:14

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

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

From

Raymond O'Donnell

Date:

02 June 2014, 14:21:56

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

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

From

Steve Crawford

Date:

02 June 2014, 18:32:16

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

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

From

Arup Rakshit

Date:

02 June 2014, 19:38:08

> 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

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

From

Arup Rakshit

Date:

04 June 2014, 09:25:58

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

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

From

Arup Rakshit

Date:

04 June 2014, 09:33:47

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

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

From

Arup Rakshit

Date:

04 June 2014, 10:15:30

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: