Hi there,
I'm experiencing problems with the ODBC driver on Windows 7 (and 10)
on 32 and 64 bit in Microsoft Access 2003, 2010 and 2016 (32, 64 bit).
My PostgreSQL server runs on Debian (64-bit): 9.6.7-1.pgdg80+1
Tested ODBC drivers: psqlodbc_09_06_0500 & psqlodbc_10_01_0000
Summary of the problem:
The current behavior of the ODBC driver creates an error in Access,
since the incoming values are longer than the field type. This leads
into a truncation warning (with many messageboxes) and no values
are shown at all. The expected behavior is either TEXT (bad) or
DOUBLE (without any precision details).
Documentation:
As stated in the docs, I've played with the setting "Unknown Sizes";
they don't help, but give me a different result:
- "Maximum" & "Longest" behave equal (see blow)
- "Don't Know" creates an error in Access (types can't be identified)
Deeper analyis:
1. column type NUMERIC (without further specification)
PostgreSQL's ODBC driver:
- field type: decimal
- precision: 28
- decimal places: 6
- decimal places display: automatic
=> I'm getting the truncation warning, no values shown (only errors).
commercial ODBC driver:
- field type: double
- decimal places display: automatic
=> Display works as expected.
2. column type NUMERIC (40, 32)
PostgreSQL's ODBC driver:
- field type: text
- length: 40
=> All values shown with trailing zeros and it's a string.
commercial ODBC driver:
=> same behaviour
3. column type NUMERIC (26, 18)
PostgreSQL's ODBC driver:
- field type: decimal
- precision: 26
- decimal places: 18
- decimal places display: automatic
=> As expected, truncation to '12345.012345678901234568'.
Lossy workaround:
A view, which casts NUMERIC to FLOAT.
Could anyone have a look please?
Best regards,
Tobias
Tested with this syntax:
DROP TABLE IF EXISTS demo;
CREATE TABLE DEMO (v numeric);
INSERT INTO demo VALUES (1.0);
INSERT INTO demo VALUES (1.0123456789);
INSERT INTO demo VALUES (1.01234567890123456789);
INSERT INTO demo VALUES (12345.012345678901234567890123456789);
ALTER TABLE demo ALTER COLUMN v TYPE numeric(40,35);
ALTER TABLE demo ALTER COLUMN v TYPE numeric(26,18);
