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:

Bin — Boolean
Datetime — Timestamp
Nchar — Char
ntext — Text
nvarchar— Varchar

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!

  • Greg

    Hiya, finding your posts pretty interesting, just thought I’d let you know that Postgres does allow NULL ints, but you have to explicitly state them as null, leaving it as “INSERT INTO (…) VALUES (…,,…)” (two ,s next to each other) won’t work…

  • Ahh I see what you mean Greg, i.e INSERT INTO table VALUES (NULL, …..) then?

    Its a good idea for some of our tables actually, some of the smaller ones anyway, but some bigger tables we use have loads of NULL values, which im understanding is pretty bad in a database anyway. The only way I can see to overcome them is do a mass table update to change their values in the table to non-null, like above (which is time consuming and doesnt seem ideal to be honest), or change the field to allow nulls and do what you said. The only issue I can see with this is the way MSSQL exports the data, because it just gives you ”,” for nulls instead of something useful such as NULL.. but im starting to see we could benifit from the info, so muchly apreciated!