How to Switch from SQL Server to PostgreSQL

This guide covers what you need to know to make the switch — from the practical differences between the two databases to the tools that make migration easier.

Why Teams Switch

SQL Server is a solid database, but it comes with trade-offs that push teams toward PostgreSQL:

• Licensing costs: SQL Server Enterprise can cost tens of thousands of dollars per core. PostgreSQL is free and open source under the PostgreSQL License.
• Platform flexibility: While SQL Server now runs on Linux, its tooling and ecosystem still lean heavily toward Windows. PostgreSQL runs natively on Linux, macOS, and Windows with equal support.
• Cloud portability: PostgreSQL is available on every major cloud provider as a managed service. You're not locked into Azure or a specific vendor's pricing.
• Extension ecosystem: PostgreSQL extensions like PostGIS, pgvector, TimescaleDB, and pg_trgm add capabilities that would require separate products or expensive add-ons in the SQL Server world.
• Community and pace of development: PostgreSQL ships major releases annually with substantial new features. The community is active, transparent, and not controlled by a single company.

Key Differences to Know

SQL Server and PostgreSQL are both relational databases, but they differ in meaningful ways. Understanding these differences upfront prevents surprises during migration.

Schemas and Databases

In SQL Server, a single instance can hold multiple databases, and cross-database queries are common. PostgreSQL also supports multiple databases per instance, but cross-database queries are not natively supported. The PostgreSQL equivalent of SQL Server's multi-database pattern is to use schemas within a single database. If your application queries across SQL Server databases, plan to consolidate into schemas.

Identity Columns and Sequences

SQL Server uses the IDENTITY property for auto-incrementing columns. PostgreSQL offers two approaches: the SERIAL pseudo-type (which creates a sequence behind the scenes) and the SQL-standard GENERATED ALWAYS AS IDENTITY syntax, which is preferred for new projects.

-- SQL Server
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100)
);

-- PostgreSQL
CREATE TABLE users (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100)
);

String Types

SQL Server has VARCHAR, NVARCHAR, CHAR, and NCHAR. The N-prefixed types store Unicode. In PostgreSQL, all text types (TEXT, VARCHAR, CHAR) are Unicode by default. There is no need for a separate NVARCHAR equivalent. Use TEXT or VARCHAR — in PostgreSQL, they perform identically.

Date and Time

SQL Server's DATETIME maps to TIMESTAMP in PostgreSQL. DATETIME2 also maps to TIMESTAMP. For time zone awareness, use TIMESTAMPTZ (short for TIMESTAMP WITH TIME ZONE), which is generally recommended as the default for any column storing points in time. SQL Server's DATETIMEOFFSET is the closest equivalent.

Boolean Type

SQL Server uses BIT for boolean values. PostgreSQL has a native BOOLEAN type that accepts TRUE, FALSE, and NULL.

T-SQL vs. PL/pgSQL

This is often the biggest part of a migration. SQL Server uses T-SQL (Transact-SQL), while PostgreSQL uses PL/pgSQL for stored procedures and functions. The languages are similar in concept but differ in syntax.

Variables and Assignment

-- T-SQL
DECLARE @count INT;
SET @count = 10;

-- PL/pgSQL
DECLARE
    count INT;
BEGIN
    count := 10;
END;

String Concatenation

T-SQL uses the + operator for string concatenation. PostgreSQL uses ||.

-- T-SQL
SELECT first_name + ' ' + last_name AS full_name FROM users;

-- PostgreSQL
SELECT first_name || ' ' || last_name AS full_name FROM users;

Top N Rows

-- T-SQL
SELECT TOP 10 * FROM orders ORDER BY created_at DESC;

-- PostgreSQL
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

Conditional Logic

T-SQL's ISNULL() becomes COALESCE() in PostgreSQL (which is also SQL-standard and works in SQL Server too). GETDATE() becomes NOW() or CURRENT_TIMESTAMP.

Stored Procedures

PostgreSQL added CREATE PROCEDURE support in version 11. Before that, everything was a function. If your SQL Server codebase relies heavily on stored procedures, they will need to be rewritten in PL/pgSQL. The logic is usually transferable, but the syntax requires careful translation.

Data Type Mapping Reference

Here is a quick reference for the most common data type conversions:

Migration Tools

You don't have to do everything by hand. Several tools can automate large parts of the migration.

pgLoader

pgLoader is an open-source tool that can migrate data directly from SQL Server to PostgreSQL. It handles schema conversion, data type mapping, and data loading in a single step. It supports continuous migration for large datasets and can cast data types automatically.

AWS Database Migration Service (DMS)

If you're moving to a cloud-hosted PostgreSQL, AWS DMS can handle ongoing replication from SQL Server to PostgreSQL. This is useful for zero-downtime migrations where you run both databases in parallel during the transition.

ora2pg

While primarily designed for Oracle migrations, ora2pg also supports SQL Server as a source. It can export schemas, data, and even attempt to convert stored procedures.

Manual Migration with pg_dump and Scripts

For smaller databases, exporting SQL Server data as CSV or using bcp and then importing with COPY or \\copy in PostgreSQL is a straightforward approach. You write the PostgreSQL schema by hand and load the data in.

Common Pitfalls

These are the issues that catch most teams during migration:

• Case sensitivity: SQL Server identifiers are case-insensitive by default. PostgreSQL folds unquoted identifiers to lowercase. If your SQL Server schema uses UserName, PostgreSQL will treat it as username unless you double-quote it. The simplest approach is to use lowercase names everywhere.
• Empty strings vs. NULL: SQL Server treats empty strings and NULL as distinct values (as it should). PostgreSQL does too, but if your application logic assumes otherwise, verify your data handling.
• Transaction behavior: In SQL Server, DDL statements (like CREATE TABLE) can be wrapped in transactions and rolled back. PostgreSQL supports transactional DDL as well, which is actually an advantage — but the behavior of implicit transactions differs. PostgreSQL auto-commits each statement unless you explicitly BEGIN a transaction.
• Temp tables: SQL Server uses #temp_table syntax. PostgreSQL uses CREATE TEMP TABLE. Temp tables in PostgreSQL are dropped at the end of the session by default.
• Error handling: T-SQL's TRY...CATCH becomes BEGIN...EXCEPTION...END in PL/pgSQL. The patterns are similar but the syntax is different.
• Pagination: If your app uses OFFSET...FETCH from SQL Server 2012+, the PostgreSQL equivalent is LIMIT...OFFSET, which is simpler and has been available since the beginning.

Testing Your Migration

A migration is only as good as its testing. Before cutting over:

• Run your application's test suite against the PostgreSQL database. Fix any SQL that doesn't translate.
• Compare row counts and checksums between source and target for every table.
• Test stored procedures and functions individually with known inputs and expected outputs.
• Benchmark query performance. PostgreSQL's query planner is different from SQL Server's, and some queries may need new indexes or restructuring.
• Run the application in a staging environment connected to the new PostgreSQL database for a full integration test.

After the Switch

Once you're on PostgreSQL, you gain access to a rich ecosystem of tools, extensions, and managed hosting options. Extensions like pgvector for AI embeddings, PostGIS for geospatial data, and pg_stat_statements for query performance analysis are all available at no additional cost.

You'll also want a good database client to work with your new PostgreSQL setup. PostgresGUI is a lightweight, native macOS client built specifically for PostgreSQL. It connects to any PostgreSQL instance — local, cloud-hosted, or running in Docker — and gives you a fast, focused interface for browsing tables, running queries, and managing your data.