How to Migrate From SQL server to PostGreSQL
As a Software development organization, we do get a lot of requests in the migration project. They are due to various reasons such as technology up-gradation, performance enhancement, and cost optimization. And for an application database is always going to be the backbone.
When a request for database migration arises a lot more things need to be verified and planned before moving ahead with such requests.
One of the similar request that we received allowed us to deep dive into this and am sharing my observations here.
SQL Server a very well known and famous Relational Database Management System (RDBMS). It’s the most popular RDBMS within the developer’s community.
PostgreSQL is an open-source database. Its community is growing powerful, strong and continuously implementing new features and improving existing features. PostgreSQL was the DBMS of the year in 2017.
Why Migrate from MS SQL Server to PostgreSQL? :
- SQL Server is a license database from Microsoft, while PostgreSQL is developed and maintained by a global community of open source developers.
- PostgreSQL is open source and completely free while MS-SQL Server cost depends on the number of users and database size.
- Benefit from open source add-ons to improve performance.
What You Should Know :
Although both SQL Server database and PostgreSQL database are ANSI-SQL compliant but there are some differences between syntax, data-types, case sensitivity and it makes transferring data not so trivial.
PostGreSQL is case sensitive. Please check some important differences before the migration.
Data Type Mapping :
Some of the data types of SQL doesn’t match directly with PostgreSQL data types, so you need to change it to corresponding PostgreSQL data type.
Please check the below table.
| SQL Server | Description | PostgreSQL Server |
|---|---|---|
| BIGINT | 64-bit integer | BIGINT |
| BINARY(n) | Fixed length byte string | BYTEA |
| BIT | 1, 0 or NULL | BOOLEAN |
| CHAR(n) | Fixed length char string, 1 | CHAR(n) |
| VARCHAR(n) | Variable length char string, 1 | CHARACTER VARYING(n) |
| VARCHAR(max) | Variable length char string, | TEXT |
| VARBINARY(n) | Variable length byte string , 1 | BYTEA |
| NVARCHAR(n) | Variable length Unicode UCS-2 string | CHARACTER VARYING(n) |
| NVARCHAR(max) | Variable length Unicode UCS-2 data, | TEXT |
| DOUBLE PRECISION | Double precision floating point number | DOUBLE PRECISION |
| FLOAT(p) | Floating point number | DOUBLE PRECISION |
| INTEGER | 32 bit integer | INTEGER |
| NUMERIC(p,s) | Fixed point number | NUMERIC(p,s) |
| DATE | Date includes year, month and day | DATE |
| DATETIME | Date and Time | TIMESTAMP WITHOUT TIME ZONE |
| DATETIMEOFFSET | Date and Time with fraction and time zone | TIMESTAMP WITH TIME ZONE |
| TINYINT | 8 bit unsigned integer, 0 to 255 | SMALLINT |
| UNIQUEIDENTIFIER | 16 byte GUID(UUID) data | UUID OR CHAR(16) |
| ROWVERSION | Automatically updated binary data | BYTEA |
| IMAGE | Variable length binary data, | BYTEA |
| INT IDENTITY(1,1) | Integer Number with Auto Identity | BIGSERIAL |
There are many incompatibilities present in SQL Server and PostgreSQL, You can see some of them here. Incompatibilities in MS SQL Server and PostgreSQL :
| Description | SQL Server | PostgreSQL Server |
|---|---|---|
| Assign value to variable. | VARIABLE = 5; | “VARIABLE” := 5; |
| Get specified part of a date. | DATEPART( datepart , date ) | DATE_PART( text , timestamp ) |
| Current Date and Time. | GETDATE() | NOW() |
| Returns a specified value if the expression is NULL. | ISNULL(exp, replacement) | COALESCE(exp, replacement) |
| Adds a time/date interval to a date. | DATEADD(day, 2, GETDATE()); | NOW() + INTERVAL ‘2 day’; |
| String Concatenation. | SELECT FirstName + LastName FROM TableName; | SELECT FirstName || LastName FROM TableName; |
| Searches a Substring. | CHARINDEX('our', 'resource') | POSITION('our' in 'resource'); |
| Get specified part of a date. | DATENAME(mm, GETDATE()); | TO_CHAR(NOW(), 'Month'); |
| Creates the Unique Value. | NEWID(); | CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; uuid_generate_v4(); |
These are a few prerequisites that a developer needs to take care of while planning to move from MS-SQL to PostGRE SQL.
Author: Parth Talaviya
