Обсуждение: 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@ejurka.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@ejurka.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
>