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