2

How to get base type / dimension of an array column in Postgres by Java?

I have a tables that contain arrays like int[][] and text[].

When I traverse the metadata from JDBC I can only get type as java.sql.Array. Even in information_schema.columns it stored simply as ARRAY.

How can I know the base type and its dimension?

I'm now working on tool to dump table info.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user1192878
  • 704
  • 1
  • 10
  • 20

3 Answers3

4

For the array base type, DatabaseMetaData.getColumns() returns a ResultSet containing column metadata. One of the columns returned is TYPE_NAME. This appears to contain the name of the array base type, prefixed with an underscore. For example, _int4 or _text. There is some additional information about the type in pg_type that may be helpful.

For the dimensions, it appears unlikely that they will be in the metadata. From the documentation:

However, the current implementation ignores any supplied array size limits, i.e., the behavior is the same as for arrays of unspecified length.

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 array_dims function will return the current dimensions of an array value. But since this could be different for every row in the table, I doubt this will help you.

UPDATE: It appears the dimensions are available in the metadata. See @a_horse_with_no_name's answer.

Community
  • 1
  • 1
Joe F
  • 4,174
  • 1
  • 14
  • 13
  • Your update is at fault, as is @a_horse on this matter. See the comment I left. – Erwin Brandstetter May 18 '13 at 14:20
  • @ErwinBrandstetter While I agree it is not enforced, the information still appears to be there. I just tested up to 1 to 3 dimensions on 9.1.9, and `attndims` is correct. – Joe F May 18 '13 at 14:25
  • It *cannot* be "correct", because there is no "correct". The column in question can hold arrays of arbitrary dimensions, no matter how you declared the column. You don't have to believe me, just try it. Information in `attndims` beyond `0` / `> 0` is only good for documentation of an *intent*. Doesn't guarantee anything about the actual values. – Erwin Brandstetter May 18 '13 at 14:27
  • @ErwinBrandstetter I understand what you are saying, and I believe you. All I am saying is the number of dimensions used to declare the column is in the metadata. I am _not_ saying that it is enforced by the database or that you cannot put more or less into it. – Joe F May 18 '13 at 14:29
  • It would be *unwise* to rely on this information in any way. People should not be instructed to use it. – Erwin Brandstetter May 18 '13 at 14:33
2

In addition to what @Joe already cleared up, you can use pg_typof() to get type information from PostgreSQL.

Given a 2-dimensional array of integer in this example:

SELECT pg_typeof(a)::text AS type
      ,(SELECT typname FROM pg_type WHERE oid = pg_typeof(a)) AS base_type
      ,array_dims(a) AS dims
FROM   (SELECT '{{11,12,13},{21,22,23}}'::int[]) x(a);

   type    | base_type |    dims
-----------+-----------+------------
 integer[] | _int4     | [1:2][1:3]

Note that the array dimensions (dims) can be different for each value. Postgres does not enforce dimensions at present (up to and incl. v9.3).
As documented in the manual, pg_attribute.attndims is of limited use here:

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".)

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
2

You can query pg_attributes directly:

select att.attname, 
       att.attndims, 
       pg_catalog.format_type(atttypid, NULL) as display_type 
from pg_attribute att 
  join pg_class tbl on tbl.oid = att.attrelid  
  join pg_namespace ns on tbl.relnamespace = ns.oid  
where tbl.relname = 'your_table_name'  
  and ns.nspname = 'table_schema'

SQLFiddle example: http://sqlfiddle.com/#!12/50301/1

Note that format_type() will actually return integer[] even though the column was defined as int[][] but the attndims column will carry the information you want.

  • `attndims` is *intended* for this purpose, but it's not implemented or enforced at present, as [the manual informs](http://www.postgresql.org/docs/current/interactive/catalog-pg-attribute.html): `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".)` – Erwin Brandstetter May 18 '13 at 14:18
  • 2
    @ErwinBrandstetter: the question was "*How to get dimension of an array column*" which is exactly what `attndims` does. The fact that this is not enforced when data is put into that column is not relevant to that question (at least not yet...) –  May 18 '13 at 15:35
  • Thank you :) yeah i didnt realize that array dimension is not enforced until now. – user1192878 May 18 '13 at 18:47