decimal precission?

[jviure]

Hi people, great extension. I can connect an mssql server from postgres, is great! but, I've in mssql server a money column that have many amounts with 3 decimals, the foreign table I've in postgres only show two decimals, I've tried numeric type, float8, decimal,... without exit, some idea? thanks!

[SudoerWithAnOpinion]

Postgres money type supports only a precision as defined by the database's LC_MONETARY setting; for example, USD is a scale of 20 with precision 2. This cannot be overridden, per the PostgreSQL documentation (8.2 - Monetary Types):

The money type stores a currency amount with a fixed fractional precision;

 value | money
-------+-------
 0.123 | $0.12
 0.125 | $0.13

This is not a tds_fdw issue, this is a PostgreSQL limitation. You may be able to work around by storing as a numeric type instead and casting to money at the last step in your queries

[jviure]

Thanks a lot for your answer! The point is that the money column is in the sql server table. When I create the foreign table in postgres, I map it to a numeric column and I only see 2 decimals, in sql server, 3. That s the issue 🙄

[SudoerWithAnOpinion]

Strange. It may even be that the server is only sending the 2. I'm sure you checked that the value being returned are actually precision 3? I.E. 1.111 and NOT 1.110

You may need to turn up the logging on the SQL server and catch the query to see what's being run.

As a work around, change the foreign table definition to use a query where you manually cast to numeric on the MSSQL side.

[SudoerWithAnOpinion]

Also, where were you doing the numeric casting? If you are doing it in the foreign table definition, postgres may be receiving it as money first (from MSSQL) and then casting it to numeric.

Honestly though, using any currency types is an implementation detail and shouldn't be used for real math (the return values in most langs/DB libs returns the string "$100.00" instead of 100.00).

[jviure]

Ups! Yes, I am doing the "supposed" conversion in the foreign table definition. In sql server is money, in postgres numeric. Otherwise, where I ve to donthe conversion? :S

[jviure]

Sorry, I read only the last post. Abou that:

As a work around, change the foreign table definition to use a query where you manually cast to numeric on the MSSQL side.

How can I do a cast in the foreig table definition? Thanks!!

[SudoerWithAnOpinion]

As a work around, change the foreign table definition to use a query where you manually cast to numeric on the MSSQL side.

There's 2 ways really...

1. Create a view on the MSSQL side that mimics the table in question and use that in your foreign table definition on the PG side.
2. Change PG's foreign table to use a query instead of a table in the options and set a simple select query that casts the value (since it will be executed on the MSSQL side first)

If you post your definitions from both sides, I'd be glad to assist with real SQL code.

[jviure]

thanks a lot! a piece of code:

https://pastebin.com/f2d1DAKH

the AlcPre column in sql server (money type) i.e I've 1.478 and postgres is 1.47.

Thanks!

[SudoerWithAnOpinion]

@jviure: It's been awhile, since it looks like we've reached a resolution, can you close this? @GeoffMontee: Got another stale one here.