Re: JPOX Types.CHAR error

On Tue, 16 May 2006, Andy Jefferson wrote:

>> A quick check reveals nothing obviously wrong with how
>> DatabaseMetaData.getTypeInfo returns data for Types.CHAR.
>
> Well that depends on your point of view ;-)
> PSQL 8.1-405 JDBC driver now returns SQL type "char" as Types.OTHER (instead
> of Types.CHAR), and returns "bpchar" as Types.CHAR. Since PSQL has always
> supported "char" as Types.CHAR (since it kinda seems reasonable), JPOX uses
> that as the default ... and it is no longer there (yet is in PSQL 8.0). I can
> set up JPOX to fake this type for users so they see no change but it would
> make more sense (to me) to have the JDBC driver return consistent with
> previous incarnations. Maybe there was a good reason why "char" is no longer
> Types.CHAR ?
>

I'm not sure there's a good reason that "char" isn't returned as
Types.CHAR, but you definitely want to be using bpchar as the default
mapping for Types.CHAR.  The type "char" is a single byte datatype
that is really only used for internal system catalogs and is not to be
confused with char(N).  One of the bugs you're probably seeing is
that the postgresql jdbc driver does not return the results of
DatabaseMetaData.getTypeInfo sorted according to the javadoc spec of
closest match, but instead returns data in random order.

jurka=# create table chartest (a "char", b char, c char(5));
CREATE TABLE
jurka=# \d chartest
       Table "public.chartest"
  Column |     Type     | Modifiers
--------+--------------+-----------
  a      | "char"       |
  b      | character(1) |
  c      | character(5) |
jurka=# select a.attname,t.typname FROM pg_attribute a, pg_type t where
a.atttypid = t.oid and a.attrelid = 'chartest'::regclass::oid and a.attnum
> 0;
  attname | typname
---------+---------
  a       | char
  c       | bpchar
  b       | bpchar
(3 rows)

Kris Jurka