Re: NUMERIC type makes trouble in MS Access

Tobias,

On Sun, May 27, 2018 at 5:43 PM, Tobias Wendorff <
tobias(dot)wendorff(at)tu-dortmund(dot)de> wrote:

> Dear Jan,
>
> Am So, 27.05.2018, 23:22 schrieb Jan Wieck:
> >
> > Nope, please read that again and then compare that statement to
> > what you wrote about the "commercial" driver (using double) and
> > your suggestion to have a switch to make the PostgreSQL ODBC
> > driver do the same (nonsense).
>
> I'm asking you not to be condescending with people who come to
> this mailing list with a problem. Not all the members on this
> lists senior postgres architects, some are just normal users.
>

sorry if that came across as condescending. It wasn't meant as such.

>
> My idea for this "nonsense" switch just was a workaround for bad
> designed databases schemas or for compatibility reasons with rarely
> used closed source applications, such as Microsoft Access.
>

In my opinion such broad "switches", that then silently mangle over all
data, are dangerous at best.

Where is the difference between truncating a number and truncating a date
to stop PostgreSQL from complaining about February 31st when importing data
from MySQL? Or a switch for the Oracle FDW to replace NUL bytes in VARCHAR2
columns with \x00 sequences?

The only difference I can see is that one is out of PostgreSQL, the other
two are into.

Regards, Jan

>
> > But what precision does Access claim to support?
>
> Access 2010 at least can handle a precision of 28 digits.
> Actually, that's what you can set on its GUI.
>
> > All I understood so far is that PostgreSQL's NUMERIC without explicit
> > precision sometimes overwhelms Access with too many digits after the
> > decimal point. If that is accurate you may want to either complain to
> > Microsoft about their insufficient implementation of DECIMAL or fix
> > the schema of the source database.
>
> I don't think that the database design is broken, since PostgreSQL
> supports NUMERIC columns without a specific precision. So all I can
> do is: create a VIEW, which rounds or casts my data on the fly to
> a format, Access can access via the ODBC driver.
>
> Best regards,
> Tobias
>
>

--
Jan Wieck
Senior Postgres Architect
http://pgblog.wi3ck.info