In order to reduce cost of ownership and take benefits of open-source software, a lot of organizations migrate their databases from SQL Server to PostgreSQL as it is the most advanced open source database. Despite of the fact that both DBMS are ANSI-SQL compliant, there are some differences in SQL syntax, data types, case sensitivity and built-in functions. Those differences make Microsoft SQL to PostgreSQL database migration not so trivial. It is very important to recognize potential issues before starting the migration procedure.
Data types
Although MS SQL and PostgreSQL have similar sets of data types, some of them don’t match and so must be mapped properly. Here is the table illustrating appropriate mapping:
| SQL Server | PostgreSQL | Description |
| BINARY(n) | BYTEA | Binary data |
| BIT | BOOLEAN | 1, 0 or NULL |
| DATETIME | TIMESTAMP(3) | Date and Time with fraction |
| FLOAT(p) | DOUBLE PRECISION | FLOAT(p) |
| IMAGE | BYTEA | Variable length binary data |
| INT IDENTITY | SERIAL | Auto-increment column |
| NVARCHAR(max) | TEXT | Variable length Unicode data |
| TINYINT | SMALLINT | 8 bit unsigned integer, 0 to 255 |
| UNIQUEIDENTIFIER | UUID | 16 byte GUID(UUID) data |
| VARBINARY(n) | BYTEA | Variable length binary data |
| VARCHAR(max) | TEXT | Variable length char string |
Built-in functions
Both SQL Server and PostgreSQL use built-in functions in queries, stored procedures and functions. The table below contains primary differences between functions in two DBMS:
| SQL Server | PostgreSQL | Description |
| CHARINDEX | POSITION | Search substring in string |
| DATEADD | operator ‘+’ | Add interval to date |
| DATEPART | DATE_PART | Extract part of the date |
| GETDATE | NOW | Get current system date |
| ISNULL | COALESCE | Replace NULL by the specified expression |
| REPLICATE | REPEAT | Generate string as replication of the specified symbol |
| SPACE(n) | REPEAT(‘ ‘, n) | Generate string as replication of the space symbol |
Finally, there are few differences in naming, default schemas and case sensitivity that must be handled correctly as well:
- MS SQL uses square brackets around object names to allow composed names or keyword, PostgreSQL uses double quotes for the same purposes
- default MS SQL schema is “dbo” while in PostgreSQL it is “public”
- unlike SQL Server, PostgreSQL is case sensitive. All names must be changed to lower case in order to avoid collisions.
This whitepaper illustrates there are many aspects in MS SQL to PostgreSQL database migration that require special attention and careful processing. Manual migration may cause a lot of problems due to human errors. This is the reason why most of database specialists use special software to automate migrating procedure.
More information about database migration from Microsoft SQL to PostgreSQL can be found at the official site of Intelligent Converters.
