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 ~]$
