Best way to migrate from MS SQL to Postgres

We are migrating the database from MS SQL to Postgres.
Currently all projects are sitting in one MS SQL database server, purely tables, no view or stored procedures.

Beside using the commercial products, is there an easy way to migrate the database and tables over?

I recommend crying and a hanky. ChatGPT helped me too :slight_smile:
There are a lot of differences though, I found. It was quite painful, and I didn't even have that many queries to migrate :confused: For one, Postgres hates table fields with capital letters which my MS SQL tables used. As soon as you add capital letter into them, you have to put quotes around the field names. So I'd recommend converting these all to lowercase, including your MS SQL table fields so it makes it easier

Unless I'm missing something, I also find Postgre painful to edit table definitions. I tried to add a column into a view (or was it a normal table?) inserted in between existing fields, and it said no. I had to drop the table first and then re-add it, but unlike MS SQL, it wouldn't do it all for me

Talk about the the database alter, For MS SQL, it also does not allow me to alter the table, except for adding new column, any column name or data type change is not allowed.

Currently all tables that I created are in lower case, good habit from mysql. Not sure tables created by other people, I will have a look.

I have followed the following link to migrate a couple of easy database before, but still looking for better or semi auto way to migrate.

You have to enable the option in SSMS:

I do this nowadays, but I didn't used to :frowning: I used CamelCase PascalCase (it's easier to type!)

thanksForTheInfo. itWorks.



That's pascalCase :wink: I used pascalCase for python variables still

Oh wait... I'm wrong. PascalCase is cap at the start, camelCase is lower-case at start

2 Likes painful to read :sweat_smile: It's all about the snake_case for me :snake:

1 Like

That's what I use now, for sql at least


Before conversion:

  • Turn on ANSI_QUOTES in SQL Server.

  • Find all queries that use square bracket quotes and replace with double-quotes. While there, add double quotes to any table or column names that have capitals. Let your application run this way a bit to make sure you didn't introduce any errors.

  • Find any other queries to tables/columns that have unquoted capital letters and fix those, too. Run a bit more.

  • Use a competent database admin tool to obtain the complete schema in DDL. I recommend DataGrip--it will produce table creation scripts with all follow-ups needed for sequences, indices, and comments.

  • If necessary, this would be the time to adjust column order in that exported schema. Also adjust datetime columns to timestamp and datetime2 to timestamptz. (You may want to convert all to timestamptz.)

  • Create the schema on the target DB. Fix errors (but keep track of what you did).


  • Export the old data as multi-row SQL inserts, with column names. In batches for large tables.

  • Run the export inserts on the target. Repeat.


Thank you for the detailed description.

I have gone through the first few steps.

In datagrip, I have obtained the ddl from ms sql database. But I missed the link on how to use datagrip to convert the ms sql generated DDL to postgres DDL. The script format is different.
Does datagrip has the function to auto convert or event the direct copy/paste or import/export function to migrate the database directly.

Currently the datagrip has been configured to link both MS SQL server and Postgres sever at the same time.

I found some informabout about copy/paste data via datagrip, but no result was found about whether I can copy/paste the database?


Just googled and found the solution, partially.

I did table by table migration, not full database migration. pretty straightforward.

1 Like

Good choice. I have to admit that I've always fixed up the schema by hand. :man_shrugging: