[orf]

> A commonly used datatype to store monetary values is the MONEY data type. In SQL Server, the MONEY data type’s behavior is fixed using four digits to the right of the decimal (e.g., $12.8123). However, in PostgreSQL, the MONEY data type is fixed using two digits to the right of the decimal.

> So, when the application tries to store a value of $12.8123, by example, PostgreSQL will round to $12.81. This subtle difference will result in a rounding error and break an application if not correctly addressed. To ensure correctness in Babelfish, we need to ensure such differences, small and large, are handled with absolute fidelity.

How are they going to solve this with just a query translation layer? Isn't information lost on save?

[dragonwriter]

> How are they going to solve this with just a query translation layer?

Well, the translation layer isn't just a query (DQL) translation layer, its an SQL Translation layer including DDL, DML, etc. Since both Postgres MONEY and SQL Server MONEY are 8-byte, fixed-precision decimal types, with the only difference being the position of the implicit decimal, a translation layer can use one as the backing store for something that is logically treated as the other without data loss, though it will have to be aware of the difference when presenting data and also when doing conversions to other datatypes, doing math other than addition/subtraction, etc.

It would be even easier, I think, to just use, what, DECIMAL(19,4) in Postgres for SQL Server MONEY, with some special handling to have the right failure behavior at the edge of the slightly-narrower range of the SQL Server MONEY type.

[arusahni]

One can also change the precision of psql's monetary type. From the docs:

> The fractional precision is determined by the database's lc_monetary setting.

[radiowave]

My guess would be: by not using Postgres's money type.

[Tostino]

I'd say the best solution here is if a really nice money data type was developed that is configurable as necessary to hopefully satisfy the MS SQL idiosyncrasies while also maybe supporting a bit richer superset of desirable features you'd want in a money type. You could even have things like temporal currency conversion type queries if you had the right feature set.

[OJFord]

Presumably by not storing SQL Server's MONEYs in pg MONEYs, but CASTing to a pg MONEY if pg asks for it.