# Best way to migrate from MS SQL to Postgres **URL:** \ **Category:** Ignition\ **Created:** [February 23, 2023, 3:47am UTC](https://forum.inductiveautomation.com/t/best-way-to-migrate-from-ms-sql-to-postgres/70202 "2023-02-23T03:47:23Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![chaoliang](https://avatars.discourse-cdn.com/v4/letter/c/ba8739/32.png) [@chaoliang](https://forum.inductiveautomation.com/u/chaoliang)\ **Post date:** [February 23, 2023, 3:47am UTC](https://forum.inductiveautomation.com/t/best-way-to-migrate-from-ms-sql-to-postgres/70202/1 "2023-02-23T03:47:23Z")
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? ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [February 23, 2023, 3:59am UTC](https://forum.inductiveautomation.com/t/best-way-to-migrate-from-ms-sql-to-postgres/70202/2 "2023-02-23T03:59:23Z")
I recommend crying and a hanky. ChatGPT helped me too 🙂 There are a lot of differences though, I found. It was quite painful, and I didn't even have that many queries to migrate 😕 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 ---
**Author:** ![chaoliang](https://avatars.discourse-cdn.com/v4/letter/c/ba8739/32.png) [@chaoliang](https://forum.inductiveautomation.com/u/chaoliang)\ **Post date:** [February 23, 2023, 4:17am UTC](https://forum.inductiveautomation.com/t/best-way-to-migrate-from-ms-sql-to-postgres/70202/3 "2023-02-23T04:17:14Z")
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. [https://wiki.postgresql.org/wiki/Microsoft\_SQL\_Server\_to\_PostgreSQL\_Migration\_by\_Ian\_Harding](https://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding) ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [February 23, 2023, 4:18am UTC](https://forum.inductiveautomation.com/t/best-way-to-migrate-from-ms-sql-to-postgres/70202/4 "2023-02-23T04:18:42Z")
> [@chaoliang](#): > > 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. You have to enable the option in SSMS: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/c/1c02d520c010a2f07d7d265bd62b72edd264da7f.png) > [@chaoliang](#): > > 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 do this nowadays, but I didn't used to ☹ I used ~~CamelCase~~ PascalCase (it's easier to type!) ---
**Author:** ![chaoliang](https://avatars.discourse-cdn.com/v4/letter/c/ba8739/32.png) [@chaoliang](https://forum.inductiveautomation.com/u/chaoliang)\ **Post date:** [February 23, 2023, 4:32am UTC](https://forum.inductiveautomation.com/t/best-way-to-migrate-from-ms-sql-to-postgres/70202/5 "2023-02-23T04:32:10Z")
thanksForTheInfo. itWorks. camelCaseIsTheMuchBetterWayToWriteScriptOrLongTagName ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [February 23, 2023, 4:33am UTC](https://forum.inductiveautomation.com/t/best-way-to-migrate-from-ms-sql-to-postgres/70202/6 "2023-02-23T04:33:27Z")
That's pascalCase 😉 I used pascalCase for python variables still Oh wait... I'm wrong. PascalCase is cap at the start, camelCase is lower-case at start ---
**Author:** ![WillMT10](https://avatars.discourse-cdn.com/v4/letter/w/7feea3/32.png) [@WillMT10](https://forum.inductiveautomation.com/u/WillMT10)\ **Post date:** [February 23, 2023, 4:52am UTC](https://forum.inductiveautomation.com/t/best-way-to-migrate-from-ms-sql-to-postgres/70202/7 "2023-02-23T04:52:51Z")
Blegh...so painful to read 😅 It's all about the snake\_case for me 🐍 ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [February 23, 2023, 5:06am UTC](https://forum.inductiveautomation.com/t/best-way-to-migrate-from-ms-sql-to-postgres/70202/8 "2023-02-23T05:06:04Z")
That's what I use now, for sql at least ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 23, 2023, 12:30pm UTC](https://forum.inductiveautomation.com/t/best-way-to-migrate-from-ms-sql-to-postgres/70202/9 "2023-02-23T12:30:10Z")
Suggestions: 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). Conversion: - 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. ---
**Author:** ![chaoliang](https://avatars.discourse-cdn.com/v4/letter/c/ba8739/32.png) [@chaoliang](https://forum.inductiveautomation.com/u/chaoliang)\ **Post date:** [February 28, 2023, 4:00am UTC](https://forum.inductiveautomation.com/t/best-way-to-migrate-from-ms-sql-to-postgres/70202/10 "2023-02-28T04:00:32Z")
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? Thanks ---
**Author:** ![chaoliang](https://avatars.discourse-cdn.com/v4/letter/c/ba8739/32.png) [@chaoliang](https://forum.inductiveautomation.com/u/chaoliang)\ **Post date:** [February 28, 2023, 4:56am UTC](https://forum.inductiveautomation.com/t/best-way-to-migrate-from-ms-sql-to-postgres/70202/11 "2023-02-28T04:56:34Z")
Just googled and found the solution, partially. I did table by table migration, not full database migration. pretty straightforward. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/0/a060d0567feb83f3fa04e2f8be796be7ff4ada49.png) ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 28, 2023, 5:42am UTC](https://forum.inductiveautomation.com/t/best-way-to-migrate-from-ms-sql-to-postgres/70202/12 "2023-02-28T05:42:44Z")
> [@chaoliang](#): > > by table migration Good choice. I have to admit that I've always fixed up the schema by hand. 🤷‍♂️