Thread: Bug report: wrong size/scale reported by DatabaseMetaData & ResultSetMetaData

The DatabaseMetaData and ResultSetMetaData report incorrect sizes/scales for
several column types. I noticed this with interval, time, timetz, timestamp,
timestamptz, and varchar columns.
I have attached a program that illustrates the problem; below this message I
pasted the output generated by running the program on my machine, and
comparing it to the column details returned by the \d command in psql.

The DatabaseMetaData reports sizes of 8 or 12 for all interval and
time/timestamp types; this appears to be the size of the types' internal
representation in bytes, instead of the column's declared size or scale. The
ResultSetMetaData returns the correct scale for all time/timestamp types,
but not for interval. The size of varchar columns is reported correctly by
DatabaseMetaData, but not by ResultSetMetaData.

I ran my tests with the PostgreSQL server 8.0.0beta1, the
postgresql-8.2dev-500.jdbc3.jar driver, and Sun JDK 1.5.0_05, on Fedora Core
3.

- Thomas Okken

------------------------------------------------------------------------
[thomas@blackbox ~]$ javac pgtest.java
[thomas@blackbox ~]$ java pgtest

Column information for time_interval_test from DatabaseMetaData:

col1: varchar(10, 0) not null
col2: time(8, 0)
col3: timetz(12, 0)
col4: timestamp(8, 0)
col5: timestamptz(8, 0)
col6: interval(12, 0)

Column information for time_interval_test from ResultSetMetaData:

col1: varchar(0, 0) not null
col2: time(0, 3)
col3: timetz(0, 3)
col4: timestamp(0, 3)
col5: timestamptz(0, 3)
col6: interval(0, 0)

[thomas@blackbox ~]$ psql
Welcome to psql 8.0.0beta1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

thomas=> \d time_interval_test
          Table "public.time_interval_test"
Column |              Type              | Modifiers
--------+--------------------------------+-----------
col1   | character varying(10)          | not null
col2   | time(3) without time zone      |
col3   | time(3) with time zone         |
col4   | timestamp(3) without time zone |
col5   | timestamp(3) with time zone    |
col6   | interval(3)                    |
Indexes:
    "time_interval_test_pkey" PRIMARY KEY, btree (col1)

thomas=> \q
[thomas@blackbox ~]$

On Sat, 3 Dec 2005, Thomas Okken wrote:

> The DatabaseMetaData and ResultSetMetaData report incorrect sizes/scales for
> several column types. I noticed this with interval, time, timetz, timestamp,
> timestamptz, and varchar columns.
> I have attached a program that illustrates the problem; below this message I
> pasted the output generated by running the program on my machine, and
> comparing it to the column details returned by the \d command in psql.

Thanks for the complete test case.  I've fixed this in CVS by unifying the
type info calculations between the two metadata classes.  I've put up some
new jar files here:

http://www.ejurka.com/pgsql/jars/to

I've applied a more limited cleanup to 8.0 and 8.1 that addresses some,
but not all of these issues, notably the internal type size is still
returned from DatabaseMetaData.  The 8.2dev cleanup also involved some
other calculation changes that I'm not sure are wise to backport.

Kris Jurka