6

I have an array of arrays [ID, TYPE].

All values are integer. Array can be integer[] or jsonb type.

SELECT array[[442,2],[443,2]]

SELECT to_jsonb(array[[1,2],[3,4]])

How can I check this array(s) contains array [443,2]?

EDIT One of the way to check described here.

1# SELECT array[[442,2],[443,2]] @> array[443,2] -- returns True
2# SELECT array[[442,2],[443,2]] @> array[2,443] -- returns True too

For my case, I want get False in 2#

0

2 Answers 2

7

Technically there is no such thing as a two-dimensional array in Postgres. Quote from the manual:

The current implementation does not enforce the declared number of dimensions either. [...] So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior

So the value array[[442,2],[443,2]] is internally stored as array[442,2,443,2] and that's the reason why array[[442,2],[443,2]] @> array[2,443] is true, because it is internally represented as array[442,2,443,2] @> array[2,443] which obviously is true.

You can however do that if you declare an array of a row type.

create type idtype as (id integer, type integer);

Then you can do the following:

select array[(442,2), (443,2)]::idtype[] @> array[(442,2)]::idtype[];
--> true

but

select array[(442,2), (443,2)]::idtype[] @> array[(2,442)]::idtype[];
--> false
1

Potential Workaround with JSONB

As a potential workaround to PostgreSQL not respecting dimensionality, you can use JSONB. As a caveat though the containment operator (@>) isn't looking for a match based on an "array". It's decomposing the array and looking for a match on the elements. So these return true

SELECT '[[4,5]]'::jsonb @> '[[5,4]]';
SELECT '[[4,3,5]]'::jsonb @> '[[5,4]]';
SELECT '[[1,2],[4,3,5],[0]]'::jsonb @> '[[5,4]]';

but this returns false,

SELECT '[[1,2],[4,3,5],[0]]'::jsonb @> '[[5,0]]';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.