Thread: 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 >