Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert SQL Server queries to Postgres on the fly

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!

like image 616
SGG Avatar asked Mar 04 '15 16:03

SGG


People also ask

How do I migrate data from SQL Server to PostgreSQL?

Run sqlserver2pgsql to generate the PostgreSQL DDL scripts and the Kettle job for the data migration. Run the generated before. sql PostgreSQL DDL script to create the db tables. Run the generated Kettle job migrate the data between the databases.

Does PostgreSQL have the same syntax as SQL?

Despite the overwhelming popularity of MySQL, PostgreSQL may be a better choice because its syntax most closely conforms to Standard SQL. This means that you can easily translate your skills to other database management systems such as MySQL or SQLite.

Is PostgreSQL syntax different from SQL Server?

PostgreSQL is an object-relational database, while Microsoft SQL Server is a relational database system. This means PostgreSQL offers more complex data types and allows object inheritance, though it also makes working with PostgreSQL more complex.

Can SQL Server connect to Postgres?

You can use the Microsoft SQL Server Management Studio to connect your PostgreSQL data to an SQL Server instance. Linked Server is a tool of MS SQL Server that allows to execute distributed queries to refer tables stored on non-SQL Server datbase in a single query.


1 Answers

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.

like image 137
Bartosz X Avatar answered Oct 13 '22 06:10

Bartosz X