Home > Documentation > MS SQL to PostgreSQL

How to convert SQL Server database to PostgreSQL

[Validate Migration]  [Types]  [Queries]  [Triggers]

Microsoft SQL is one of the world's most popular database management systems with user-friendly interface that is easy to learn and use. However, it has certain disadvantages such as high cost of ownership for large databases and strict licensing policy. These facts may lead some users to the need of shifting from MS SQL to another DBMS.

Why PostgreSQL?

When planning a database migration, it is logical to review open-source systems in order to reduce total cost of ownership. There are three major and important open-source RDBMS: SQLite, MySQL and PostgreSQL.

SQLite is a self-contained file-based database system that is designed for embedding into applications. So it should not be considered as replacement for large databases in multi-user environment.

MySQL is far more powerful than SQLite and it provides a lot of features that are expected from advanced RDBMS such as security, scalability, wide range of supported storage types for different purposes. However, it does not implement the full SQL standard, does not provide full text search and has poor support for concurrent writes in some database engines.

PostgreSQL is a very powerful standards-compliant RDBMS providing integral object-oriented and/or relational database functionality. This all make it the best choice for projects that require high level of reliability and data integrity.

SQL Server to PostgreSQL Migration Steps

Here is the sequence of steps to migrate database from Microsoft SQL to PostgreSQL:

Now let's consider these steps more closely. This is how you can export MS SQL table definitions:

  1. in SQL 2008 and earlier you can script objects and data. Right-click on database in Management Studio, Tasks, Generate Scripts. Go through the wizard and make sure to check "data" which is false by default.
  2. in SQL 2012 and later: right-click on database in Management Studio, Tasks, Generate Scripts. On the "Set scripting options" tab click on Advanced, then select "data only", or "data and schema" for "Types of data to script" (in the General section).

The resulting script must be corrected before loading to PostgreSQL as follows:

Now is the time to process data. It also can be done via Microsoft SQL Management Studio: right-click on database, Tasks, Export Data. Go through the wizard and select "Microsoft OLE DB Provider for SQL Server" as data source, "Flat File Destination" as destination. After the export is completed, you can see all data exported into the specified file in comma-separated values (CSV) format.

If some table contains binary data it is required to apply the workaround. Select "Write a query to specify the data to transfer" option on the wizard page called "Specify Table Copy or Query". On the next wizard page called "Provide a Source Query" you should compose SELECT-query as follows:

select <non-binary field #1>, <non-binary field #2>, cast(master.sys.fn_varbintohexstr(
	cast(<binary field name> as varbinary(max))) as varchar(max)) as <binary field name> 
from <table name>;

Unfortunately, this approach is not applied for large binary data (1MB+) since the query goes into infinite hang. You can import the resulting CSV file into PostgreSQL table via "COPY" command:

COPY <table name> FROM <path to csv file> DELIMITER ',' CSV;

If you see "Permission denied" error after running this statement, try to use "\COPY" command instead.

SQL Server to PostgreSQL Conversion Tools

As you may see, database migration from Microsoft SQL to PostgreSQL is a complicated time-consuming process requiring a lot of efforts. Manual conversion is costly and slow process, it can lead to incorrect results and cause data loss or corruption. Fortunately, there are many tools to convert MS SQL to PostgreSQL, which can migrate data between those two DBMS with just a few clicks of mouse button. Intelligent Converters offer one of such solutions - SQL Server to PostgreSQL conversion tool.