Convert SQL Server queries to Postgres on the fly

Question

I have a scenario where I get queries on a webservice that need to be executed on a database.

The source for these queries is from a physical device so I cant really change the input to my queries.

I get the queries from the device in MSSQL. Earlier the backend was in SQL Server, so things were pretty straight forward. Queries would come in and get executed as is on the DB.

Now we have migrated to Postgres and we don't have to the option to modify the input data (SQL queries).

What I want to know is. Is there any library that will do this SQL Server/T-SQL translation for me so I can run the SQL Server queries through this and execute the resulting Postgres query on the database. I searched a lot but couldn't find much that would do this. (There are libraries that convert schema from one to another but what I need is to be able to translate SQL Server queries to Postgres on the fly)

I understand there are quite a bit of nuances that will be different between SQL and postgres so a translator will be needed in between. I am open to libraries in any language(that preferably runs on linux : ) ) or if you have any other suggestions on how to go about this would also be welcome.

Thanks!

Answer 1

If I were in your position I would have a look on upgrading your SQL Sever to 2019 ASAP (as of today, you can find on Twitter that the officially supported production ready version is available on request). Then have a look on the Polybase feature they (re)introduced in this version. In short words it allows you to connect your MSSQL instance to other data source (like Postgres) and query the data in as they would be "normal" SQL Server DB (via T-SQL) then in the background your queries will be transformed into the native pgsql and consumed from your real source. There is not much resources on this product (as 2019 version) yet, but it seems to be one of the most powerful features coming with this release.

This is what BOL is saying about it (unfortunately, it mostly covers the old 2016 version).

There is an excellent, yet very short presentation by Bob Ward ( Principal Architect @ Microsoft) he did during SQL Bits 2019 on this topic.