Thread: PostgreSQL data types mapped Java classes for JDBC

Hi, all.


Where are all the PostgreSQL data types mapped to Java classes? I have found the following from an old mailing list message:

1 data_type_id Data Type Id java.lang.Integer int4 11


2 smallint_type Smallint Type java.lang.Integer int2 6
3 int_type Int Type java.lang.Integer int4 11
4 bigint_type Bigint Type java.lang.Long int8 20
5 decimal_type Decimal Type java.math.BigDecimal numeric 18


6 numeric_type Numeric Type java.math.BigDecimal numeric 12
7 real_type Real Type java.lang.Float float4 14
8 doubleprecision_type Doubleprecision Type java.lang.Double float8 24
9 serial_type Serial Type java.lang.Integer int4 11


10 bigserial_type Bigserial Type java.lang.Long int8 20
11 varchar_type Varchar Type java.lang.String varchar 30
12 char_type Char Type java.lang.String bpchar 30
13 text_type Text Type java.lang.String text 2147483647


14 bytea_type Bytea Type [B bytea 2147483647
15 date_type Date Type java.sql.Date date 13
16 time_type Time Type java.sql.Time time 15
17 timetz_type Timetz Type java.sql.Time timetz 21
18 timestamp_type Timestamp Type java.sql.Timestamp timestamp 29


19 timestamptz_type Timestamptz Type java.sql.Timestamp timestamptz 35
20 interval_type Interval Type org.postgresql.util.PGInterval interval 49
21 boolean_type Boolean Type java.lang.Boolean bool 1

24 box_type Box Type org.postgresql.geometric.PGbox box 2147483647
25 path_type Path Type org.postgresql.geometric.PGpath path 2147483647

28 cidr_type Cidr Type java.lang.Object cidr 2147483647
29 inet_type Inet Type java.lang.Object inet 2147483647
30 macaddr_type Macaddr Type java.lang.Object macaddr 2147483647


31 bit2_type Bit2 Type java.lang.Boolean bit 2
32 bitvarying5_type Bitvarying5 Type java.lang.Object varbit 5

Where is the most recent list?

Thank you!
Dave

P.S.
If these are not already in the information_schema (or similar), I think they would make for a worthwhile addition.

Individual adaptions should not be stored external to PostgreSQL. It would be trivial to set up a relation that:

1. Maintains a current list of PostgreSQL data types.
2. Maintains a list of adapters (perhaps with effective dating).
3. Maps adapters to Java types.
   

This would centralize the knowledge and facilitate dynamic implementations that, for whatever reason, need to know the correct data type mapping in an adapter-agnostic fashion. (Ideally adapters would map the PostgreSQL data types to Java classes the same way; centralizing the list would reveal to everyone the differences in the adapters without having to scour the web.)

This doesn't have to be packaged with PostgreSQL distributions: it could be offered as a separate script that people can run to update the information schema.

  On 10/08/10 11:33 AM, Thangalin wrote:
> If these are not already in the information_schema (or similar), I
> think they would make for a worthwhile addition.

The information_schema doesn't contain anything having to do with client
side data type bindings, nor should it.    *completely* outside its
domain.      nothing in the database server could possibly know what
JDBC is doing on the client side.  in fact, in theory its quite possible
two different client connections are using two completely different JDBC
implementations which have different bindings.  Yes, I know, theory !=
practice, yada yada.

the place for that sort of metadata would be in the jdbc driver doing
the data binding, or in associated documentation files (xml, perhaps?)

That's the problem, John.

No single location provides a consistent way to know what all those bindings are and how they differ. If you wanted to write dynamic code that generates the correct mapping, you cannot readily create any such implementation. You must:

1. Find the metadata for the mapping. (Probably a half hour of research, or more, per driver.)
2. Define the relation for the data type bindings (a couple of hours, tops).
   
3. Convert the metadata from source code comments, XML, or whatever to a table relation (and verify that the details are up to date). (Possibly several hours.)
   
4. Debug and test that the implementation is correct. (Several more hours.)
5. Maintain the list and retest each time either the adapters change or PostgreSQL changes. (That is, retest what someone else has already tested.)
   

Let me emphasize what I wrote:


As the information_schema is ill-suited, I offered the following alternative:


The idea is to maintain this information such that it:

1. Is up to date
2. Is centralized
3. Can be queried with ease
   

It matters little how it is implemented, technically. It could be (in my preferred order):

• a set of tables in a custom jdbc schema;
  
• a set of SQL scripts for CREATE TABLE and INSERT statements;
• a set of XML files that conform to an XML schema definition;
• an OpenOffice or Google Docs document (structured to be machine-readable); or
  
• a wiki page.

To clarify:

• List all known PostgreSQL-JDBC adapters.
• Map data types to corresponding Java classes for each adapter.
• Maintain the list in a central, public location (ideally that can be machine transformed into a relational database).
• Distribute the burden of maintaining the list on the shoulders of the adapter developers.
  

Dave

Thangalin wrote:
> Hi, all.
>
>
> Where are all the PostgreSQL data types mapped to Java classes? I have
> found the following from an old mailing list message:
>
> 1 data_type_id Data Type Id java.lang.Integer int4 11
>
>
>
>
>
That list was generated by my application which I use to do exactly
what is indicated. Create/track changes that might occur in the JDBC
and database backend for the data types. The application creates the
data types mapping dynamically for each table and its critical that
I know if changes occur. I have not created the re-generated list for
the current 9.0 release, yet. I have the basics scripts and the app
generates the list. It usually only takes a few minutes. I could probably
put something together so others could run has needed to check
their configurations. I haven't checked lately if all the data types
are covered, I know enum aren't in the list.

danap.

Thangalin wrote:
> That's the problem, John.
>
> No single location provides a consistent way to know what all those
> bindings are and how they differ. If you wanted to write dynamic code
> that generates the correct mapping, you cannot readily create any such
> implementation.

Use DatabaseMetadata, it's intended for exactly this case.

Oliver