Query for sub-array in multi-dimentional array

Question

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);

Answer 1

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:

• PostgreSQL two dimensional array intersection