Array Char/VarChar Size

Given a defined table as such:

CREATE TABLE array_types (

data_type_id serial NOT NULL,

decimal_array decimal(16,2)[] DEFAULT NULL,
numeric_array numeric(10,2)[] DEFAULT NULL,
varchar_array varchar(30)[] DEFAULT NULL,
char_array char(30)[][] DEFAULT NULL,
PRIMARY KEY (data_type_id)
);

How do I obtain the precision, decimal places, or character
sizing information from the database. Notice these are array
types.

danap.

On Wed, 27 Feb 2008, dmp wrote:

> Given a defined table as such:
>
> CREATE TABLE array_types (
>
> data_type_id serial NOT NULL,
> decimal_array decimal(16,2)[] DEFAULT NULL,
> numeric_array numeric(10,2)[] DEFAULT NULL,
> varchar_array varchar(30)[] DEFAULT NULL,
> char_array char(30)[][] DEFAULT NULL,
> PRIMARY KEY (data_type_id)
> );
>
> How do I obtain the precision, decimal places, or character
> sizing information from the database. Notice these are array
> types.

Currently the driver does not return this information, but we've listed it
as an open feature request:

http://pgfoundry.org/tracker/index.php?func=detail&aid=1010248&group_id=1000224&atid=857

Kris Jurka

Perhaps this is not the appropriate list, but is there a way to
determine this information
from the information_schema? The reason I ask is I can not seem to find
this information
there either. pg_dump does provide this information, though lacking the
ability to proper
identify multi-dimensional arrays, ex. char(30)[][] gives char(30)[].

danap.

> Currently the driver does not return this information, but we've
> listed it as an open feature request:
>
> http://pgfoundry.org/tracker/index.php?func=detail&aid=1010248&group_id=1000224&atid=857
>
> Kris Jurka

> Given a defined table as such:
>
> CREATE TABLE array_types (
>
> data_type_id serial NOT NULL,
> decimal_array decimal(16,2)[] DEFAULT NULL,
> numeric_array numeric(10,2)[] DEFAULT NULL,
> varchar_array varchar(30)[] DEFAULT NULL,
> char_array char(30)[][] DEFAULT NULL,
> PRIMARY KEY (data_type_id)
> );
>
> How do I obtain the precision, decimal places, or character
> sizing information from the database. Notice these are array
> types.
>
> danap.

On Fri, 29 Feb 2008, dmp wrote:

> Perhaps this is not the appropriate list, but is there a way to
> determine this information from the information_schema? The reason I ask
> is I can not seem to find this information there either. pg_dump does
> provide this information, though lacking the ability to proper identify
> multi-dimensional arrays, ex. char(30)[][] gives char(30)[].

Multi-dimensional array information is not stored. Every array type may
be any number of dimensions. The precision information is not available
in the information_schema, but it is available in the system catalog
tables. See pg_attribute.atttypmod, but it does require some decoding.

Kris Jurka

Kris Jurka <books(at)ejurka(dot)com> writes:
> Multi-dimensional array information is not stored. Every array type may
> be any number of dimensions. The precision information is not available
> in the information_schema, but it is available in the system catalog
> tables. See pg_attribute.atttypmod, but it does require some decoding.

Rather than embedding knowledge of typmod encoding in client-side code,
may I suggest using the format_type function? For example

select format_type(atttypid, atttypmod) from pg_attribute where
attrelid = 'my_table'::regclass and attname = 'my_column';

This will give you back something reasonably standardized, like
"character varying(42)[]". You'll still need a bit of logic to
extract what you want, but it seems much less likely to break.

regards, tom lane

Mr. Lane,
I used your suggestion and it works nicely. From a client perspective
I would prefer to see support for this type of information from the
JDBC for longevity, robustness, and isolation purposes. Currently
the JDBC returns only NULLs.

Again thanks guys, I appreciate the help.
danap.

>Kris Jurka <books(at)ejurka(dot)com> writes:
>
>
>>> Multi-dimensional array information is not stored. Every array type may
>>> be any number of dimensions. The precision information is not available
>>> in the information_schema, but it is available in the system catalog
>>> tables. See pg_attribute.atttypmod, but it does require some decoding.
>>
>>
>
>Rather than embedding knowledge of typmod encoding in client-side code,
>may I suggest using the format_type function? For example
>
>select format_type(atttypid, atttypmod) from pg_attribute where
>attrelid = 'my_table'::regclass and attname = 'my_column';
>
>This will give you back something reasonably standardized, like
>"character varying(42)[]". You'll still need a bit of logic to
>extract what you want, but it seems much less likely to break.
>
> regards, tom lane
>