Thread: Column types via ODBC interface
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
Matt Goodall <mgg@isotek.co.uk> writes:
> f10 decimal(9,2) SQL_VARCHAR, 254 SQL_DECIMAL, 9
> f11 numeric(9,2) SQL_VARCHAR, 254 SQL_DECIMAL, 9
The 6.5 ODBC driver doesn't know about type numeric, so it returns its
default assumption, which is varchar as above. The 7.0 version knows
about numeric, however.
The SQL type returned for "bool" appears to be a user-settable option.
The choices made for floating types look reasonably plausible.
If you think they're wrong, you'll need to argue why, not just
assert that you think they are. It's easy enough to change the
ODBC driver's SQL type <=> Postgres type mapping if there's a
better definition than what we're using...
regards, tom lane
Tom Lane wrote: > > Matt Goodall <mgg@isotek.co.uk> writes: > > f10 decimal(9,2) SQL_VARCHAR, 254 SQL_DECIMAL, 9 > > f11 numeric(9,2) SQL_VARCHAR, 254 SQL_DECIMAL, 9 > > The 6.5 ODBC driver doesn't know about type numeric, so it returns its > default assumption, which is varchar as above. The 7.0 version knows > about numeric, however. Great - I'll upgrade to v 7.x then. > The SQL type returned for "bool" appears to be a user-settable option. Sorry if I've missed something obvious in the docs but is that a postgres setting or an odbc driver manager setting? > The choices made for floating types look reasonably plausible. > If you think they're wrong, you'll need to argue why, not just > assert that you think they are. It's easy enough to change the > ODBC driver's SQL type <=> Postgres type mapping if there's a > better definition than what we're using... > > regards, tom lane Fair enough - I should have explained my reasoning here. Here's an bit from table 3-1.Postgres Data Types in the 6.5.3 documentation: Postgres Type SQL92 or SQL3 Type ~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~ ... float4/8 float(p) float8 real, double precision ... I was expecing that to mean that float4/8 would map to an ODBC SQL_FLOAT and float8 would map to either SQL_REAL or SQL_DOUBLE. 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
Matt Goodall <mgg@isotek.co.uk> writes:
> Sorry if I've missed something obvious in the docs but is that a
> postgres setting or an odbc driver manager setting?
odbc side.
> I was expecing that to mean that float4/8 would map to an ODBC SQL_FLOAT
> and float8 would map to either SQL_REAL or SQL_DOUBLE.
The code in odbc's pgtypes.c maps float4 to SQL_REAL and float8 to
SQL_FLOAT. Perhaps that's backward, or perhaps not; do the ODBC specs
say anything about the expected precision associated with SQL_REAL
or SQL_FLOAT?
regards, tom lane