Skip site navigation (1) Skip section navigation (2)

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

From: "Thomas Okken" <thomas_okken(at)hotmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Bug report: wrong size/scale reported by DatabaseMetaData & ResultSetMetaData
Date: 2005-12-03 14:39:00
Message-ID: BAY107-F1732A8C0B7CDF5ED81CA23F84F0@phx.gbl (view raw, whole thread or download thread mbox)
Lists: pgsql-jdbc
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 

- Thomas Okken

[thomas(at)blackbox ~]$ javac
[thomas(at)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(at)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)                    |
    "time_interval_test_pkey" PRIMARY KEY, btree (col1)

thomas=> \q
[thomas(at)blackbox ~]$

Description: application/octet-stream (3.2 KB)


pgsql-jdbc by date

Next:From: Kris JurkaDate: 2005-12-04 20:31:07
Subject: Re: Bug report: wrong size/scale reported by DatabaseMetaData
Previous:From: Giuseppe SaccoDate: 2005-12-03 11:53:32
Subject: new italian translation

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group