Open-source software becomes extremely popular nowadays. Following this trend, a lot of organizations migrate their databases from SQL Server to PostgreSQL as it is the most powerful open source DBMS. This procedure can help to reduce cost of ownership and take advantages of ANSI SQL standard compliance system. Due to some differences in data types, case sensitivity and built-in functions MS SQL to PostgreSQL migration not so trivial as it may seem at first glance. It is vital to recognize all possible issues before beginning the migration procedure.
Types Conversion
SQL Server and PostgreSQL have similar data types, however some of them don’t match and must be converted properly. Here is the table of correct conversions:
| Microsoft SQL | PostgreSQL | Comments |
| 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 | Unique identifier |
| VARBINARY(n) | BYTEA | Binary data of variable length |
| VARCHAR(max) | TEXT | Text data of variable length |
Built-in Functions
Built-in functions are used in queries, stored procedures and functions. The table below contains equivalents of those functions in SQL Server and PostgreSQL:
| Microsoft SQL | PostgreSQL | Comments |
| CHARINDEX | POSITION | Locates substring in string |
| DATEADD | operator ‘+’ | Adds interval to date |
| DATEPART | DATE_PART | Extracts part of the date |
| GETDATE | NOW | Returns current system date |
| ISNULL | COALESCE | Replaces NULL values by the specified expression |
| REPLICATE | REPEAT | Returns replication of the specified symbol |
| SPACE(n) | REPEAT(‘ ‘, n) | Returns replication of space symbol |
These two DBMS also have different restrictions on object names, default schemas and case sensitivity. All those aspects must be handled correctly as follows:
- MS SQL encloses object names in square brackets to allow space symbols or keyword, PostgreSQL uses double quotes for the same purposes
- The default schema in SQL Server is “dbo” while PostgreSQL defines it as “public”
- PostgreSQL names are case sensitive while in MS SQL they are not. In view of this fact, all names must be converted to lower case to avoid collisions.
This guide explores primary challenges of MS SQL to PostgreSQL database migration. Running the procedure manually may cause data loss or corruption due to human errors. In order to eliminate those risks many database specialists use special software to automate migration process.
Visit http://www.convert-in.com/docs/mss2pgs/intro.htm for more information about database migration from Microsoft SQL to PostgreSQL
