Can query postgresql for column array dimensions? (test if column is int[][]?)

Question

In postgres, how can I query whether a columns contains int[][] (as opposed to int[]?)

I can query for information about the types of columns:

select *
from INFORMATION_SCHEMA.COLUMNS where table_name = 'mytable'

and I see udt_name and data_type jointly provide the base type of the columns. But none of these columns say what the arity of an array column is (arity would be 2 for int[][] and 1 for int[], and zero for int).

Postgres clearly has this information as I can see it if I view a table's schema in pgadmin3.

klin · Accepted Answer · 2017-05-31 08:35:04Z

3

You can use array_ndims()

select 
    array_ndims(array[1,2]) as "int[]", 
    array_ndims(array[[1],[2]]) as "int[][]"

 int[] | int[][] 
-------+---------
     1 |       2
(1 row)

The number of dimensions of an array column is stored in the system catalog pg_attribute, e.g.:

create table test(a int[], b int[][], c int[][][]);

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 c.oid = 'test'::regclass
and attnum > 0;

 attname | typname | attndims 
---------+---------+----------
 a       | _int4   |        1
 b       | _int4   |        2
 c       | _int4   |        3
(3 rows)

The value of attndims reflects the way how the column was declared and may differ from dimensionality of actual values:

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

select array_ndims(a) as a, array_ndims(b) as b, array_ndims(c) as c
from test;

 a | b | c 
---+---+---
 1 | 1 | 1
(1 row)

edited May 31, 2017 at 8:35

answered May 30, 2017 at 22:14

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pozs Over a year ago

Maybe also worth mentioning that attndims is more like a documentation attribute. Presently, the number of dimensions of an array is not enforced, so any nonzero value effectively means "it's an array".

klin Over a year ago

The value of attndims reflects the way how the column was declared in create table. I think the note in the documentation refers to the fact that in Postgres dimensionality is not forced regardless of how it was declared.

pozs Over a year ago

Yes, exactly. As I said (or at least, intended) values above 1 servers only documentation. Because actual column values' dimensions can differ from it.

Collectives™ on Stack Overflow

Can query postgresql for column array dimensions? (test if column is int[][]?)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related