Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Babelfish: SQL Server-to-Postgres Translation Layer

aws.amazon.com

Post reply on HN
Print view View original

61–70 of 118 posts

Re: Babelfish: SQL Server-to-Postgres Translation Layer

#61

Post by throwaway201103 »

JonathonW wrote: post #44
linuxhiker wrote: post #38

This is interesting because it will also help Sybase migrations. SQL Server is the "brand name" but there are still a lot of people stuck on Sybase.

Will it? MSSQL and Sybase diverged somewhere around 27 years ago; anything that Sybase and Microsoft did differently since then would likely be completely incompatible.

I would guess at least 80% of common SQL and T-SQL from Sybase is still completely compatible with SQL Server.

As a side note, I had no idea Sybase still existed at all. Looks like it's now part of SAP's portfolio.

Re: Babelfish: SQL Server-to-Postgres Translation Layer

#62

Post by OJFord »

orf wrote: post #59

> 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 differ…

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

Re: Babelfish: SQL Server-to-Postgres Translation Layer

#63

Post by radiowave »

orf wrote: post #59

> 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 differ…

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

Re: Babelfish: SQL Server-to-Postgres Translation Layer

#64

Post by [deleted] »

orf wrote: post #59

> 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 differ…

[deleted]

Re: Babelfish: SQL Server-to-Postgres Translation Layer

#65

Post by dragonwriter »

orf wrote: post #59

> 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 differ…

> 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.

Re: Babelfish: SQL Server-to-Postgres Translation Layer

#66

Post by [deleted] »

orf wrote: post #59

> 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 differ…

[deleted]

Re: Babelfish: SQL Server-to-Postgres Translation Layer

#69

Post by tyldum »

Yeroc wrote: post #8
mjasay wrote: post #6

Earlier quoted context omitted.

Correct. The focus is on 100% correctness. As I wrote in the post: "Over its 35 years in existence, SQL Server has evolved to meet a wide array of use cases. When first made available on GitHub, Babelfish won’t be able to handle every use case, but will be able to tackle the most common application scenarios. Most importantly, Babelfish will meet the correctness objective. That is, if Babelfish doesn’t yet support sp…

Interesting. Now where's the Oracle-to-Postgres Translation project? ;-)

I have used ora2pg with great success.
Post reply on HN