Column types via ODBC interface - Mailing list pgsql-general

Hi,

I'm trying to query the table definition of a table in a postgres
database via the ODBC interface but I'm getting some unexpected results.
I'd be grateful for any comments/ideas.

I'm using postgresql 6.5.3, RedHat Linux 6.2, libiodbc 2.50.3. For the
mysql test I'm using version 3.22.32.

Here's a table which shows the column name, the type used for "create
table" and what the odbc interface returns for a database hosted by
postgresql and mysql respectively.

column    column        postgres ODBC      mysql ODBC
name      type          type, size         type, size
~~~~~~~~  ~~~~~~~~~~~~  ~~~~~~~~~~~~~~~~~  ~~~~~~~~~~~~~~~~~
f1        bool          SQL_CHAR, 1        SQL_TINYINT, 1
f2        char          SQL_CHAR, 1        SQL_CHAR, 1
f3        char(10)      SQL_CHAR, 10       SQL_VARCHAR, 10
f4        date          SQL_DATE, 10       SQL_DATE, 10
f5        float         SQL_FLOAT, 15      SQL_REAL, 10
f6        float(3)      SQL_REAL, 7        SQL_REAL, 10
f7        real          SQL_FLOAT, 15      SQL_DOUBLE, 16
f8        smallint      SQL_SMALLINT, 5    SQL_SMALLINT, 6
f9        int           SQL_INTEGER, 10    SQL_INTEGER, 11
f10       decimal(9,2)  SQL_VARCHAR, 254   SQL_DECIMAL, 9
f11       numeric(9,2)  SQL_VARCHAR, 254   SQL_DECIMAL, 9
f12       time          SQL_TIME, 8        SQL_TIME, 8
f13       timestamp     SQL_TIMESTAMP, 19  SQL_TIMESTAMP, 19
f14       varchar(100)  SQL_VARCHAR, 100   SQL_VARCHAR, 100

Now, I was surprised by the mysql results for columns f3, f5 and f7 but
at least they're about right i.e. a double isn't too different from a
float and a char(10) can be stored in a varchar(10).

Unfortunately, the postgresql results seem to be fairly seriously wrong
for columns f1, f7, f10 and f11 and a bit wrong for column f6. IMHO :-).
After all, you can't insert a value into a decimal(9,2) with psql using
"90.2" - you have to write it without the quotes which makes sense.

Can anyone elightenment me about these results. Perhaps MySQL is wrong
and PostgreSQL is right? Does the Postgres ODBC v 7 interface fix things
(if it's broken)? Am I just an idiot that has done something completely
wrong :-) ?

TIA for any help.

Cheers, Matt.

--
Matt Goodall             |  Isotek Electronics Ltd
email: mgg@isotek.co.uk  |  Claro House, Servia Road
Tel: +44 113 2343202     |  Leeds, LS7 1NL
Fax: +44 113 2342918     |  England