The reason for our previous problems now became apparent.
This is down to data types; PostgreSQL has a different data type for some of the fields, such as:
MSSQL — POSTGRESQL
Bin — Boolean
Datetime — Timestamp
Nchar — Char
ntext — Text
This wasnâ€™t the end of the world, it just meant that each field in our database that would need changing, would need to be checked to see if it met the constraints of the PostgreSQL data time. One other thing to note is, a lot of these fields donâ€™t allow NULLS, which is also the same for int (int is the same data type in both databases but MSSQL allows NULLS, PostgreSQL definitely doesnâ€™t.
The quick fix for this was to change every NULL value (that we could) into a 0 (zero) or another number that would tie into our database. Maybe not the best solution, but these fields werenâ€™t being used properly any way (hence the NULLS).
2 days later after changing multiple fields and multiple records in MSSQL, I was ready to try again. I ran the script, got all the information into the .txt file and ran the file.
No Errors. A quick SELECT COUNT(*) from customers; showed 20000 records. SUCCESS!