Click here to Skip to main content
13,202,723 members (57,108 online)
Click here to Skip to main content
Add your own
alternative version

Stats

12.5K views
829 downloads
8 bookmarked
Posted 31 Dec 2015

Convert SQL Server Database to PostgreSQL

, 2 Jan 2016
Rate this:
Please Sign up or sign in to vote.
This console application converts an SQL Server database to PostgreSQL.

Introduction

This console application converts an SQL Server database to PostgreSQL. The AdventureWorksLT2008R2 database was used for testing.

The application uses Npgsql (.NET data provider for PostgreSQL). Npgsql will be installed automatically by the NuGet package manager.

Background

I first tried CSV export and import to convert an SQL Server database to PostgreSQL, but this failed to copy binary fields properly as PostgreSQL expects binary data in its own proprietary format.

Command Line Usage

CopySqlServerToPostgresql [catalogname] [catalogname postgres]

If the second argument is omitted, the same name is used for the PostgreSQL database.

Example:

CopySqlServerToPostgresql AdventureWorksLT2008R2

Using the Code

Both SQL Server (or SQL Server Express) and PostgreSQL need to be installed (PostgreSQL portable can also be used).

The application uses Windows Authentication and tries to connect to SQL Server Express first, if the connection fails, it tries to connect to standard SQL Server. If you need SQL Server authentication, you will have to change the program code.

A new PostgreSQL database with tables and Primary Keys will be created. Microsoft SQL Server datatypes will be translated to their PostgreSQL equivalents. The data will be copied into the newly created database.

This takes about 10 seconds for the AdventureWorksLT2008R2 database (on a notebook with an I5 processor).

In debug mode, change the Catalog variable in Main() to copy a database of your own choice:

#if DEBUG
            // In debug mode use AdventureWorksLT2008R2 catalog.
            Catalog = "AdventureWorksLT2008R2";
            Catalog2 = Catalog.ToLower();

An overview of SQL Server data types and their PostgreSQL equivalents:

char                char / text
nchar               char / text
varchar             varchar / text
nvarchar            varchar / text
xml                 text
int                 integer
bigint              bigint
bit                 boolean
uniqueidentifier    uuid
hierarchyid         bytea
geography           bytea
tinyint             smallint
float               float
real                real
double              double precision
numeric             numeric
decimal             numeric
money               numeric
smallmoney          numeric
binary              bytea
varbinary           bytea
image               bytea
datetime            timestamptz
datetime2           timestamptz

Note that for char and varchar conversion, I kept the same data types, although it is also possible to use the more flexible PostgreSQL text data type which behaves like varchar(max).

If you feel limited by this, feel free to change the code at the end of the GetFieldInformation() method.

Defaults

Getdate() will be converted to the PostgreSQL equivalent Now().

ROWGUIDCOL and newid() will be converted to the PostgreSQL equivalent uuid_generate_v1().

To enable support for generating GUIDs, in the OpenPostgreSql() method, the necessary extension is installed:

//-- Needed for: uuid PRIMARY KEY DEFAULT uuid_generate_v1()
ExecuteSqlScript(connectionStringPostgresql, "CREATE EXTENSION \"uuid-ossp\";");

IDENTITY columns will be converted to the PostgreSQL equivalent SERIAL.

Note that SERIAL is not a real data type, behind the scenes it is an integer with a sequence attached to it.

Points of Interest

Not supported:

  • Stored procedures
  • User defined functions

You will have to create these yourself, or change the program code, it's probably best to get this information directly using a query from the SQL Server database.

Another possible use of this application might be to "downgrade" an SQL Server database to a lower version, e.g. 2012 to 2008.
This would be even simpler to accomplish, as the PostgreSQL data type conversion is not needed.

Thanks to Henry for the Schema suggestion!

Tested on Windows 7 with VS2013, SQL Server Express 2008 R2 and PostgreSQL 9 portable.

More information can be found at the following links:

History

Version 1

  • Initial release, source code only

Version 1.1

  • Added command line arguments
  • Added executable (uses Windows Authentication)
  • Fixed order of primary key fields (for combined primary key)

Version 1.2

  • Fixed a bug for fields with a data type in the name, e.g. datetime
  • Added support for both standard SQL Server and SQL Server Express

Version 1.3

  • Added legacy data type "image"
  • Added support for schemas
  • Added support for indexes
  • Added support for constraints: foreign keys, defaults, ROWGUIDCOL

Version 1.4

  • Added support for Views
  • Added support for IDENTITY columns
 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

RickZeeland
Software Developer
Netherlands Netherlands
Software developer in the Netherlands, currently working on Video Surveillance applications.
Experience: C#, C++, VB, ASP, SQL Server, PostgreSQL.
It all started with Black&White, no not the whiskey but the Sinclair ZX81 followed by several Atari's and PC's. The journey continues ...

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionNo data found at Destination Pin
Member 1314023325-Apr-17 20:04
memberMember 1314023325-Apr-17 20:04 
AnswerRe: No data found at Destination Pin
RickZeeland26-Apr-17 7:00
professionalRickZeeland26-Apr-17 7:00 
GeneralRe: No data found at Destination Pin
Member 1314023326-Apr-17 20:09
memberMember 1314023326-Apr-17 20:09 
GeneralRe: No data found at Destination Pin
Member 1314023331-May-17 1:10
memberMember 1314023331-May-17 1:10 
GeneralRe: No data found at Destination Pin
RickZeeland31-May-17 1:21
professionalRickZeeland31-May-17 1:21 
Questionplease, the inverse operation is possible? pleease Pin
antani7413-Apr-17 0:08
memberantani7413-Apr-17 0:08 
AnswerRe: please, the inverse operation is possible? pleease Pin
RickZeeland13-Apr-17 1:40
professionalRickZeeland13-Apr-17 1:40 
GeneralRe: please, the inverse operation is possible? pleease Pin
antani7413-Apr-17 3:43
memberantani7413-Apr-17 3:43 
GeneralRe: please, the inverse operation is possible? pleease Pin
RickZeeland13-Apr-17 7:21
professionalRickZeeland13-Apr-17 7:21 
Questionconvert functions and stored procudures from SQLServer to PostgreSQL Pin
michel_kouame26-Jan-17 5:45
membermichel_kouame26-Jan-17 5:45 
AnswerRe: convert functions and stored procudures from SQLServer to PostgreSQL Pin
RickZeeland26-Jan-17 7:01
professionalRickZeeland26-Jan-17 7:01 
PraiseA great time saver! Pin
whidbey8816-Jan-17 6:58
memberwhidbey8816-Jan-17 6:58 
SuggestionVote of 3 stars Pin
HenryWDC5-Jan-16 6:30
memberHenryWDC5-Jan-16 6:30 
PraiseRe: Vote of 3 stars Pin
RickZeeland5-Jan-16 8:57
professionalRickZeeland5-Jan-16 8:57 
GeneralRe: Vote of 3 stars Pin
HenryWDC6-Jan-16 7:46
memberHenryWDC6-Jan-16 7:46 
GeneralRe: Vote of 3 stars Pin
RickZeeland6-Jan-16 8:42
professionalRickZeeland6-Jan-16 8:42 
GeneralRe: Vote of 3 stars Pin
RickZeeland10-Jan-16 2:04
professionalRickZeeland10-Jan-16 2:04 
QuestionPlease give a reason for low votes ! Pin
RickZeeland4-Jan-16 7:02
professionalRickZeeland4-Jan-16 7:02 
PraiseRe: Please give a reason for low votes ! Pin
Win32nipuh19-Aug-16 19:59
memberWin32nipuh19-Aug-16 19:59 
GeneralRe: Please give a reason for low votes ! Pin
RickZeeland20-Aug-16 7:51
professionalRickZeeland20-Aug-16 7:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.171020.1 | Last Updated 2 Jan 2016
Article Copyright 2015 by RickZeeland
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid