Wrong scale for numeric data types in MS Access and ADODB - Mailing list pgsql-odbc

Hi,

I'm mostly convinced this is a bug, but I just need to verify (and
maybe find a workaround??). I am trying to link a postgres table with
a numeric column into Microsoft Access 2003 using the PostgreSQL
Unicode ODBC driver, version 8.04.02.00 (12/27/2009). Here is the
table and data in PostgreSQL 8.4.

CREATE TABLE chem
(
  id serial NOT NULL,
  "name" text NOT NULL,
  atomic_weight numeric,
  CONSTRAINT chem_pkey PRIMARY KEY (id),
  CONSTRAINT chem_name_key UNIQUE (name)
);

INSERT INTO chem("name", atomic_weight) VALUES
('Arsenic',74.92159),
('Sodium', 22.98976928),
('Tritium',3.016049);


The data looks normal in pgAdmin, so there is no problem on the server end.

On my Microsoft Windows test laptop, I've set up a System DSN using
the PostgreSQL Unicode ODBC driver to the database using all defaults,
setting only the required fields. In MS Access 2003, I can link
public.chem as public_chem, but viewing the linked table shows me
this:

id    name    atomic_weight
1    Arsenic    74.92159
#Error    #Error    #Error
3    Tritium    3.016049

There are three rows, but the second shows #Error across all lines,
and if I click on that row I see an error dialog "Scaline of decimal
value resulted in data truncation" eight times. If I insert another
row (using psql):
INSERT INTO chem("name", atomic_weight) VALUES ('Cesium',132.9054519);

this also has an error, since there are 7 decimal places used.
However, I can insert more lines with 6 or fewer decimal places
without error.

Viewing the MS Access linked table in [read-only] "Design mode" of
reveals that the numeric field "atomic_weight" has a precision of 28
and a scale of 6. This is not correct (or modifiable), since there are
more than 6 decimal places used in the database for my example data.
This is why the rows with Sodium and Cesium have errors, while Arsenic
and Tritium are good, since there are 6 or less decimal places used.

Investigating further using VBA/ADODB shows a similar underling scale issue:

Sub test()
    Dim conn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As ADODB.Recordset

    conn.ConnectionString = "Driver={PostgreSQL
Unicode};Server=192.168.123.1;Port=5432;Database=mydb;Uid=myuser;Pwd=mypass;"
    conn.Open

    Set cmd.ActiveConnection = conn
    cmd.CommandText = "SELECT * FROM chem WHERE name='Sodium';"

    Set rs = cmd.Execute()
    Set cmd = Nothing

    Debug.Print rs("name"), rs("atomic_weight"),
rs("atomic_weight").NumericScale

    rs.Close
    Set rs = Nothing

    conn.Close
    Set conn = Nothing
End Sub

The console debug output shows:
Sodium         22.98976928   6

meaning that the NumericScale of the field is also set at 6. Again,
this scale of 6 is incorrect, but the output is correct and no errors
are thrown by this sub.

How can I modify the scale for this column? Is this a bug, and with
what component? Are there any known workarounds besides crippling my
data to a smaller scale?

Thanks for taking a look!

-Mike