Thread: DatabaseMetaData.getTypeInfo() question
Hi, We are currently testing the upcoming version of DbVisualizer with PostgreSQL and the JDBC drivers. We have one concern and that is the information returned from the DatabaseMetaData.getTypeInfo() method. It returns a lots of information, some are data type info while some are tables or functions(?). We did a quick comparision of the supported data types as listed in the PostgreSQL documenation and what is returned by the getTypeInfo call. The result is rather confusing for us. (http://www.postgresql.org/docs/7.3/static/datatype.html) - Among the real data types we find types such as: TYPE_NAME DATA_TYPE PRECISION pg_cast 1111 9 pg_class 1111 9 pg_constraint 1111 9 pg_conversion 1111 9 pg_database 1111 9 What is this and why do they appear when getting type info? - The documentation lists data types as "Type Name" and "Aliases". Why do the getTypeInfo() call return the aliased name for almost all data types that have an alias instead of the in most cases preferred "Type Name"? I.e we believe that users coming from other databases would prefeer "bigint" rather then "int8", "smallint" rathern then "int2", etc. There are some exceptions to this such as the "varchar" alias which is more common then "character varying". One solution is simply to add the real type names to the existing list of data types as returned by the getTypeInfo() call. Any feedback is appreciated. Roger Bjärevall Minq Software -- Roger Bjärevall Product Manager Minq Software AB Stockholm, Sweden Phone: +46 8 55602760 Fax: +46 8 55602777 roger.bjarevall@minq.se http://www.minq.se
Interesting, I just happened to read some code or docs which explains this. Every table when created also creates an entry in pg_type. Postgres supports inheritance, so every table is a type. Dave On Thu, 2003-10-09 at 04:40, Roger Bjärevall wrote: > Hi, > > We are currently testing the upcoming version of DbVisualizer with > PostgreSQL and the JDBC drivers. > > We have one concern and that is the information returned from the > DatabaseMetaData.getTypeInfo() method. > > It returns a lots of information, some are data type info while some > are tables or functions(?). > > We did a quick comparision of the supported data types as listed in the > PostgreSQL documenation and what is returned by the getTypeInfo call. > The result is rather confusing for us. > > (http://www.postgresql.org/docs/7.3/static/datatype.html) > > - Among the real data types we find types such as: > > TYPE_NAME DATA_TYPE PRECISION > pg_cast 1111 9 > pg_class 1111 9 > pg_constraint 1111 9 > pg_conversion 1111 9 > pg_database 1111 9 > > What is this and why do they appear when getting type info? > > - The documentation lists data types as "Type Name" and "Aliases". > Why do the getTypeInfo() call return the aliased name for almost > all data types that have an alias instead of the in most cases > preferred "Type Name"? > I.e we believe that users coming from other databases would prefeer > "bigint" rather then "int8", "smallint" rathern then "int2", etc. > There are some exceptions to this such as the "varchar" alias which > is more common then "character varying". > > One solution is simply to add the real type names to the existing list > of data types as returned by the getTypeInfo() call. > > Any feedback is appreciated. > > Roger Bjärevall > Minq Software > > > > > >
On Thu, 9 Oct 2003, [ISO-8859-1] Roger Bj�revall wrote: > Hi, > > We are currently testing the upcoming version of DbVisualizer with > PostgreSQL and the JDBC drivers. > > We have one concern and that is the information returned from the > DatabaseMetaData.getTypeInfo() method. > > It returns a lots of information, some are data type info while some > are tables or functions(?). > > We did a quick comparision of the supported data types as listed in the > PostgreSQL documenation and what is returned by the getTypeInfo call. > The result is rather confusing for us. > > (http://www.postgresql.org/docs/7.3/static/datatype.html) > > - Among the real data types we find types such as: > > TYPE_NAME DATA_TYPE PRECISION > pg_cast 1111 9 > pg_class 1111 9 > pg_constraint 1111 9 > pg_conversion 1111 9 > pg_database 1111 9 > > What is this and why do they appear when getting type info? In postgresql all tables have a corresponding complex type with values for each of the columns. So you're seeing the complex types associated with the various system tables. > - The documentation lists data types as "Type Name" and "Aliases". > Why do the getTypeInfo() call return the aliased name for almost > all data types that have an alias instead of the in most cases > preferred "Type Name"? > I.e we believe that users coming from other databases would prefeer > "bigint" rather then "int8", "smallint" rathern then "int2", etc. > There are some exceptions to this such as the "varchar" alias which > is more common then "character varying". > > One solution is simply to add the real type names to the existing list > of data types as returned by the getTypeInfo() call. The getTypeInfo call only returns information about postgresql's internal types. For example int8 is the internal type name for an eight byte integer. The fact that the parser also allows you to specify bigint is information that is not available to the jdbc driver or really anything other than the parser. Perhaps the type table in the documentation is not entirely clear that bigint is an alias for int8 and not the other way around. Kris Jurka