Columns with domains from other schemas and DatabaseMetaData - Mailing list pgsql-jdbc

From Thomas Kellerer
Subject Columns with domains from other schemas and DatabaseMetaData
Date
Msg-id i9rf7e$bac$1@dough.gmane.org
Whole thread Raw
List pgsql-jdbc
Hi,

I have noticed another problem with DatabaseMetaData.getColumns() and domains.

If a domain is created in a different schema than the table, psql will show the fully qualified name of the domain when
doinga "\d table" 

When retrieving the column definition of such a table using getColumns() the column TYPE_NAME from the obtained
resultsetwill contain the domain name without the schema. 

As an example:

CREATE SCHEMA other;
CREATE DOMAIN other.salary_domain AS numeric(12,2) NOT NULL CHECK (value > 0);
CREATE TABLE employee (id integer not null, salary other.salary_domain);
COMMIT;

 From psql, the following is displayed for this table:

psql (9.0.1)
Type "help" for help.

postgres=> \d employee
          Table "public.employee"
  Column |        Type         | Modifiers
--------+---------------------+-----------
  id     | integer             | not null
  salary | other.salary_domain |


postgres=>

Unfortunately the JDBC driver does not return the fully qualified name of the domain as psql does.
Although according to the JDBC Javacods, TYPE_NAME is described as "for a UDT the type name is fully qualified".

This is the sample Java code:

Connection con = DriverManager.getConnection(...)
ResultSet rs = con.getMetaData().getColumns(null, "public", "employee", "%");
while (rs.next())
{
   String col = rs.getString("COLUMN_NAME");
   String type = rs.getString("TYPE_NAME");
   System.out.println(col + ": " + type);
}

Will print:

id: integer
salary: salary_domain


I tried this with PostgreSQL 9.0.1 and the 9.0-801 JDBC driver.

It would nice if this could be fixed in a future version of the driver.

Thanks
Thomas

pgsql-jdbc by date:

Previous
From: dmp
Date:
Subject: Re: Bug in DatabaseMetaData.getColumns() with columns based on domains
Next
From: Michael musset
Date:
Subject: Re: JDBC4 with SQLXML and postgresql-8.4-702.jdbc4.jar