PostgreSQL two dimensional array intersection

Question

is it possible with postgres tools to intersect two-dimensional arrays?

For instance I have:

id               arr
1    {{1,2}, {3,4}, {4,5}, {4,7}}
2    {{4,2}, {7,4}, {8,5}, {9,7}}

...

And I want to get all records that have {4,5} in theirs array, record id=1 here.
If I do something like:

select * from table where arr && '{4,5}'

I'll get both of those example records. It finds all records where are 4 and 5 are anywhere in the array - as if it was looking in fully extracted arrays, like {1,2,3,4,4,5,4,7}.

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

I thought of using functions from the extension intarray, but:

Many of these operations are only sensible for one-dimensional arrays. Although they will accept input arrays of more dimensions, the data is treated as though it were a linear array in storage order.

My other ideas:

Quick and dirty

WITH x(id, arr) AS (VALUES
    (1, '{{1,2}, {3,4}, {4,5}, {4,7}}'::int[])
   ,(2, '{{4,2}, {7,4}, {8,5}, {9,7}}')
   )
SELECT * 
FROM   x
WHERE  arr::text LIKE '%{4,5}%';

The simple trick is to transform the array to its text representation and check with LIKE.

With array-functions

WITH x(id, arr) AS (
    VALUES
     (1, '{{1,2}, {3,4}, {4,5}, {4,7}}'::int[])
    ,(2, '{{4,2}, {7,4}, {8,5}, {9,7}}')
    )
    ,y AS (
    SELECT id, arr, generate_subscripts(arr, 1) AS i
    FROM   x
)
SELECT id, arr
FROM   y
WHERE  arr[i:i] = '{{4,5}}'::int[];

Or, the same in different notation, building on your example:

SELECT id, arr
FROM  (
    SELECT id, arr, generate_subscripts(arr, 1) AS i
    FROM   tbl
    ) x
WHERE  arr[i:i] = '{{4,5}}'::int[];

This generates the array subscripts for the first dimension. With the help of this set-returning function we check each array-slice. Note the double brackets on the right hand side of the expression to create a two-dimensional array that matches.

If {4,5} can be in arr multiple times, add GROUP BY or a DISTINCT clause to avoid duplicate rows.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 9, 2012 at 12:20

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1587242 Over a year ago

yes, trick with string types is what i was thinking too, but i was sure, it is not a good solution and there is something better. thanks a lot

Collectives™ on Stack Overflow

PostgreSQL two dimensional array intersection

1 Answer 1

Quick and dirty

With array-functions

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Quick and dirty

With array-functions

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related