2

How do I fix the following sql query:

DROP TABLE IF EXISTS array_test;

CREATE TABLE array_test (
  id serial primary key,
  name text,
  array_column integer[][]
);

insert into array_test (name, array_column) VALUES
  ('rowa', '{{1,2,3},{4,5,6}}'),
  ('rowb', '{{4,5,6},{7,8,9},{10,11,12}}');

This following query triggers a syntax error. How do I query for values with this sub array?

select * from array_test where '{1,2,3}' = ANY (array_column);
0

1 Answer 1

0

Here is one way in Postgres 9.3+ with generate_subscripts():

SELECT a.*
FROM   array_test a
JOIN   LATERAL generate_subscripts(a.array_column, 1) i
               ON a.array_column[i:i] = '{{1,2,3}}'::int[];

This way I am comparing whole slices of a 2-dimensional array. Note the double curly braces!: '{{1,2,3}}'. LATERAL and a join condition is probably the shortest syntax. If your slices are not unique within each array, rather use an EXISTS semi-join to get unique rows in the result (also works with older versions):

SELECT a.*
FROM   array_test a
WHERE  EXISTS (
   SELECT 1
   FROM   generate_subscripts(a.array_column, 1) i
   WHERE  a.array_column[i:i] = '{{1,2,3}}'::int[]
   );

If you are unfamiliar with the Postgres array implementation, this may seem contrived. Read on ...

Defining a column as integer[][] (signifying a 2-dimenstional array) is effectively the same as just integer[], because Postgres does currently not differentiate array data types with different dimensions. Each array type can have any number of dimensions as long as they match up internally.

Per documentation:

The current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior.

The simple expression:

WHERE 1 = ANY (array_column)

... tests for equality with any base element, ignoring dimensions completely. Per documentation

The left-hand expression is evaluated and compared to each element of the array using the given operator.

Bold emphasis mine. It's just not possible to compare array slices this way.
Similar with array operators:

WHERE '{1,2,3}'::int[] <@ array_column

... would match for all arrays that include all of the base elements, again regardless of dimensions.

Per documentation:

Array comparisons compare the array contents element-by-element, using the default B-tree comparison function for the element data type. In multidimensional arrays the elements are visited in row-major order (last subscript varies most rapidly). If the contents of two arrays are equal but the dimensionality is different, the first difference in the dimensionality information determines the sort order.

Bold emphasis mine.

Related older answer on SO with some other techniques:

Your Answer

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

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