NUMERIC type makes trouble in MS Access

"Tobias Wendorff" <[email protected]>
Newsgroups gmane.comp.db.postgresql.odbc
Message-ID <[email protected]>
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);
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.