Postgres array comparison confusion

Question

When I run

select array[19,21,500] <= array[23,5,0];

I get true.

but when I run

select array[24,21,500] <= array[23,5,0];

I get false. This suggests that the comparison is only on the first element.

I am wondering if there is an operator or possibly function that compares all the entries such that if all the entries in the left array are less than those in the right array (at the same index) it would return true, otherwise return false.

I'm hoping to retrieve all the rows that have an entire array "less than" or "greater than" a given array. I don't know if this is possible.

It's not the first element, it's the first that differs, as successive characters in a string comparison. According to the doc: http://www.postgresql.org/docs/9.1/static/functions-array.html there is no built-in operator doing what you want, but nothing prevents from writing one. It doesn't look like something hard to do, at least in procedural code (plpgsql). The semantics would need to be elaborated a bit, though, to cover the case of different number of elements or dimensions between the two arrays. — Daniel Vérité, Mar 22 '12 at 17:42
Thanks. I'll explore some of the geometric data types, see if I can't get something there. — user1286546, Mar 22 '12 at 17:53

score 2 · Answer 1 · answered Mar 26 '13 at 01:39

Arrays use ordinality as a basic property. In other words '{1,3,2}' <> '{1,2,3}' and this is important to understand when looking at comparisons. These look at successive elements.

Imagine for a moment that PostgreSQl didnt have an inet type. We could use int[] to specify cidr blocks. For example, we could see this as '{10,0,0,1,8}' to represent 10.0.0.1/8. We could then compare IP addresses on this way. We could also represent as a bigint as: '{167772161,8}' In this sort of comparison, if you have two IP addresses with different subnets, we can compare them and the one with the more specific subnet would come after the one with the less specific subnet.

One of the basic principles of database normalization is that each field should hold one and only one value for its domain. One reason arrays don't necessarily violate this principle is that, since they have ordinality (and thus act as a tuple rather than a set or a bag), you can use them to represent singular values. The comparisons make perfect sense in that case.

In the case where you want to create an operator which does not respect ordinality, youc an create your own. Basically you make a function that returns a bool based on the two, and then wrap this in an operator (see CREATE OPERATOR in the docs for more on how to do this). You are by no means limited by what PostgreSQL offers out of the box.

score 1 · Answer 2 · answered Mar 26 '13 at 05:53

To actually conduct the operation you asked for, use unnest() in parallel and aggregate with bool_and():

SELECT bool_and(a <  b) -- each element < corresponding element in 2nd array
      ,bool_and(a <= b)
      ,bool_and(a >= b)
      ,bool_and(a >  b)
       -- etc.
FROM  (SELECT unnest('{1,2,3}'::int[]) AS a, unnest('{2,3,4}'::int[]) AS b) t

Both arrays need to have the same number of base elements to be unnested in parallel. Else you get a CROSS JOIN, i.e. a completely different result.

Postgres array comparison confusion

2 Answers2