未加星标

Migrating from MSSQL to PostgreSQL - What You Should Know

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

As you may know, Microsoft SQL Server is very popular RDBMS with highly restrictive licencing and high cost of ownership if the database is of significant size, or is used by a significant number of clients. It provides a very user-friendly interface and easy to learn. This has resulted in a large installed user base.

PostgreSQL is the world's most advanced open source database. The PostgreSQL community is very strong and continuously improving existing features and implementing new features. As per db-engine popularity rank , PostgreSQL was the DBMS of the year 2017.

Why Migrate from MS SQL Server to PostgreSQL? MS SQL Server is a proprietary database from Microsoft, while PostgreSQL is developed and maintained by a global community of open source developers. If cost is an issue, then definitely you should go with PostgreSQL. You can check the pricing here . PostgreSQL is a cross platform database engine and it is available for windows, Mac, Solaris, FreeBSD and linux while SQL Server only runs on Windows operating system. As you may know, PostgreSQL is open source and completely free while mssql Server cost depends on the number of users and database size. Flexible open source licencing and easy availability from public cloud providers like AWS, Google cloud etc. Benefit from open source add-ons to improve performance. What You Should Know

Although both Microsoft SQL Server database and PostgreSQL database are ANSI-SQL compliant but there are still differences between their SQL syntax, data types, case sensitivity, and it makes transferring data not so trivial.

Before migration, understand the differences between MSSQL and PostgreSQL. There are many features in both databases so you should know the behaviour of those features/functions in MSSQL and PostgreSQL. Please check some important differences you should know before migration.

Data Type Mapping

Some of the data types of MSSQL don’t match directly with PostgreSQL data types, so you need to change it to corresponding PostgreSQL data type.

Please check the below table.

Microsoft SQL Server PostgreSQL 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 <= n <= 8000 CHAR(n) VARCHAR(n) Variable length char string, 1 <= n <= 8000 VARCHAR(n) VARCHAR(max) Variable length char string, <= 2GB TEXT VARBINARY(n) Variable length byte string , 1 <= n <= 8000 BYTEA VARBINARY(max) Variable length byte string , <= 2GB BYTEA NVARCHAR(n) Variable length Unicode UCS-2 string VARCHAR(n) NVARCHAR(max) Variable length Unicode UCS-2 data, <= 2GB TEXT TEXT Variable length character data, <= 2GB TEXT NTEXT Variable length Unicode UCS-2 data, <= 2GB 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 with fraction TIMESTAMP(3) DATETIME2(p) Date and Time with fraction TIMESTAMP(n) DATETIMEOFFSET(p) Date and Time with fraction and time zone TIMESTAMP(p) WITH TIME ZONE SMALLDATETIME Date and Time TIMESTAMP(0) TINYINT 8 bit unsigned integer, 0 to 255 SMALLINT UNIQUEIDENTIFIER 16 byte GUID(UUID) data CHAR(16) ROWVERSION Automatically updated binary data BYTEA SMALLMONEY 32 bit currency amount MONEY IMAGE Variable length binary data, <= 2GB BYTEA

Download the Whitepaper Today

PostgreSQL Management & Automation with ClusterControl

Learn about what you need to know to deploy, monitor, manage and scale PostgreSQL

Download the Whitepaper

Incompatibilities in MS SQL Server and PostgreSQL

There are many incompatibilities present in MS SQL Server and PostgreSQL, You can see some of them here. You can automate them by creating extensions so that you can use the MS SQL Server function as it is in PostgreSQL and you can save your time.

DATEPART

DATEPART must be replaced by DATE_PART in PostgreSQL.

Example

MS SQL:

DATEPART( datepart , date )

PostgreSQL:

date_part( text , timestamp ) date_part( text , interval ) ISNULL

ISNULL function must be replaced by COALESCE function in PostgreSQL.

Example

MS SQL Server:

ISNULL(exp, replacement)

PostgreSQL:

COALESCE(exp, replacement) SPACE

SPACE function in MS SQL Server must be replaced by REPEAT function in PostgreSQL.

Example

MS SQL Server:

SPACE($n)

Where $n is the number of spaces to be returned.

PostgreSQL:

REPEAT(‘ ’, $n) DATEADD

PostgreSQL does not provide DATEADD function similar to MS SQL Server, you can use datetime arithmetic with interval literals to get the same results.

Example

MS SQL Server:

--Add 2 day to the current date SELECT DATEADD(day, 2, GETDATE());

PostgreSQL:

--Add 2 day to the current date SELECT CURRENT_DATE + INTERVAL ‘2 day’; String Concatenation

MS SQL Server uses ‘+’ for String Concatenation whereas PostgreSQL uses ‘||’ for the same.

Example

MS SQL Server:

SELECT FirstName + LastName FROM employee;

PostgreSQL:

SELECT FirstName || LastName FROM employee; CHARINDEX

There is CHARINDEX function in PostgreSQL. You can replace this function by PostgreSQL equivalent POSITION function.

Example

MS SQL Server:

SELECT CHARINDEX('our', 'resource');

PostgreSQL:

SELECT POSITION('our' in 'resource'); GETDATE

GETDATE function returns the current date and time. There is no GETDATE function in PostgreSQL, but there is NOW() function for the same purpose. If there are multiple occurrences of the GETDATE function then you can automate them using extension. Please check how to create modules using extension .

Example

MS SQL Server:

SELECT GETDATE();

PostgreSQL:

SELECT NOW(); Tools

You can use some tools to migrate MS SQL Server database to PostgreSQL. Please test the tool before use it.

Pgloader

You can use the pgloader tool to migrate MS SQL database to PostgreSQL. The commands in the pgloader load the data from MS SQL database. Pgloader supports automatic discovery of the schema, including build of the indexes, primary key and foreign keys constraints.

Pgloader provides various casting rules which can convert the MS SQL data type to a PostgreSQL data type.

Sqlserver2pgsql

This is another open source migration tool to convert Microsoft SQL Server database into a PostgreSQL database, as automatically as possible. Sqlserver2pgsql is written in Perl.

Sqlserver2pgsql tool does two things:

It converts a SQL Server schema to a PostgreSQL schema It can produce a Pentaho Data Integrator (Kettle) jib to migrate all the data from SQL Server to PostgreSQL. This is an optional part.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Migrating from MSSQL to PostgreSQL - What You Should Know
本站链接:https://www.codesec.net/view/610838.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(152)