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.
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.
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:
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
double double precision
Note that for
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
If you feel limited by this, feel free to change the code at the end of the
Getdate() will be converted to the PostgreSQL equivalent
newid() will be converted to the PostgreSQL equivalent
To enable support for generating GUIDs, in the
OpenPostgreSql() method, the necessary extension is installed:
ExecuteSqlScript(connectionStringPostgresql, "CREATE EXTENSION \"uuid-ossp\";");
IDENTITY columns will be converted to the PostgreSQL equivalent
SERIAL is not a real data type, behind the scenes it is an
integer with a sequence attached to it.
Points of Interest
- 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:
- Initial release, source code only
- Added command line arguments
- Added executable (uses Windows Authentication)
- Fixed order of primary key fields (for combined primary key)
- Fixed a bug for fields with a data type in the name, e.g.
- Added support for both standard SQL Server and SQL Server Express
- Added legacy data type "image"
- Added support for schemas
- Added support for indexes
- Added support for constraints: foreign keys, defaults,
- Added support for Views
- Added support for