Query for sub-array in multi-dimentional array 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);
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[] &lt;@ 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