Lists: | pgsql-jdbc |
---|
From: | dmp <danap(at)ttc-cmc(dot)net> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Array Char/VarChar Size |
Date: | 2008-02-27 07:35:38 |
Message-ID: | 47C512CA.9010808@ttc-cmc.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
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.
From: | Kris Jurka <books(at)ejurka(dot)com> |
---|---|
To: | dmp <danap(at)ttc-cmc(dot)net> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Array Char/VarChar Size |
Date: | 2008-02-29 16:59:17 |
Message-ID: | Pine.BSO.4.64.0802291158350.30735@leary.csoft.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
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
From: | dmp <danap(at)ttc-cmc(dot)net> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Array Char/VarChar Size |
Date: | 2008-02-29 17:32:15 |
Message-ID: | 47C8419F.2060909@ttc-cmc.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
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.
From: | Kris Jurka <books(at)ejurka(dot)com> |
---|---|
To: | dmp <danap(at)ttc-cmc(dot)net> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Array Char/VarChar Size |
Date: | 2008-02-29 17:53:57 |
Message-ID: | Pine.BSO.4.64.0802291248520.30295@leary.csoft.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Kris Jurka <books(at)ejurka(dot)com> |
Cc: | dmp <danap(at)ttc-cmc(dot)net>, pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Array Char/VarChar Size |
Date: | 2008-02-29 18:44:27 |
Message-ID: | 10183.1204310667@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
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
From: | dmp <danap(at)ttc-cmc(dot)net> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Array Char/VarChar Size |
Date: | 2008-03-01 22:10:41 |
Message-ID: | 47C9D461.1000502@ttc-cmc.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
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
>