How to get the dimensionality of an ARRAY column?

Question

I'm working on a project that collects information about your schema from the database directly. I can get the data_type of the column using information_schema.columns, which will tell me if it's an ARRAY or not. I can also get the underlying type (integer, bytea etc) of the ARRAY by querying information_schema.element_types as described here:

https://www.postgresql.org/docs/9.1/static/infoschema-element-types.html

My problem is that I also need to know how many dimensions the array has, whether it is integer[], or integer[][] for example. Does anyone know of a way to do this? Google isn't being very helpful here, hopefully someone more familiar with the Postgres spec can lead me in the right direction.

Answer 1

For starters, the dimensionality of an array is not reflected in the data type in Postgres. The syntax integer[][] is tolerated, but it's really just integer[] internally.
Read the manual here.

This means that dimensions can vary within the same array type (the same table column).

To get actual dimensions of a particular array value:

SELECT array_dims(my_arr);  -- [1:2][1:3]

Or to just get the number of dimensions:

SELECT array_ndims(my_arr);  -- 2

There are more array functions for similar needs. See table of array functions in the manual.

Related:

• Use string[][] with ngpsql

If you need to enforce particular dimensions in a column, add a CHECK constraint. To enforce 2-dimensional arrays:

ALTER TABLE tbl ADD CONSTRAINT tbl_arr_col_must_have_2_dims
CHECK (array_ndims(arr_col) = 2);

Answer 2

Multidimensional arrays support in Postgres is very specific. Multidimensional array types do not exist. If you declare an array as multidimensional, Postgres casts it automatically to a simple array type:

create table test(a integer[][]);
\d test

      Table "public.test"
 Column |   Type    | Modifiers 
--------+-----------+-----------
 a      | integer[] |     

You can store arrays of different dimensions in a column of an array type:

insert into test values
    (array[1,2]),
    (array[array[1,2], array[3,4]]);

select a, a[1] a1, a[2] a2, a[1][1] a11, a[2][2] a22
from test;

       a       | a1 | a2 | a11 | a22 
---------------+----+----+-----+-----
 {1,2}         |  1 |  2 |     |    
 {{1,2},{3,4}} |    |    |   1 |   4
(2 rows)

This is a key difference between Postgres and programming languages like C, python etc. The feature has its advantages and disadvantages but usually causes various problems for novices.

You can find the number of dimensions in the system catalog pg_attribute:

select attname, typname, attndims
from pg_class c
join pg_attribute a on c.oid = attrelid
join pg_type t on t.oid = atttypid
where relname = 'test'
and attnum > 0;

 attname | typname | attndims 
---------+---------+----------
 a       | _int4   |        2
(1 row)

It is not clear whether you can rely on this number, as for the documentation:

attndims - Number of dimensions, if the column is an array type; otherwise 0. (Presently, the number of dimensions of an array is not enforced, so any nonzero value effectively means "it's an array".)