advanced 2-6 weeks depending on stored procedure complexity100% Free Guide

How to Migrate from SQL Server to PostgreSQL

Migrating from SQL Server to PostgreSQL is a popular path for organizations moving away from Microsoft's proprietary platform to open-source. The main effort involves converting T-SQL syntax, stored procedures (to PL/pgSQL), and handling SQL Server-specific features.

10

Data Type Mappings

8

Syntax Differences

6

Migration Steps

2

Free Tools

Data Type Mappings

SQL ServerPostgreSQLNotes
INT IDENTITY(1,1)SERIAL or GENERATED ALWAYS AS IDENTITY—
NVARCHAR(n)VARCHAR(n)PostgreSQL uses UTF-8 encoding by default — no N prefix needed
NVARCHAR(MAX)TEXT—
BITBOOLEANConvert 0 → false, 1 → true
DATETIME / DATETIME2TIMESTAMP or TIMESTAMPTZUse TIMESTAMPTZ for timezone-aware data
UNIQUEIDENTIFIERUUIDPostgreSQL has native UUID type; use gen_random_uuid()
VARBINARY(MAX)BYTEA—
ROWVERSION / TIMESTAMPUse trigger + INTEGER version columnPostgreSQL has no ROWVERSION equivalent
MONEY / SMALLMONEYNUMERIC(19,4)NUMERIC is more precise than FLOAT for financial data
TINYINT (0-255)SMALLINT (with CHECK 0-255)PostgreSQL SMALLINT is signed; use CHECK constraint for 0-255 range

SQL Syntax Differences

TOP N

SQL Server

SELECT TOP 10 * FROM t ORDER BY col

PostgreSQL

SELECT * FROM t ORDER BY col LIMIT 10
ISNULL

SQL Server

ISNULL(col, 'default')

PostgreSQL

COALESCE(col, 'default')

COALESCE is ANSI standard

String functions

SQL Server

LEN(str), LEFT(str,n), RIGHT(str,n)

PostgreSQL

LENGTH(str), LEFT(str,n), RIGHT(str,n)

LEN → LENGTH

Date functions

SQL Server

GETDATE(), DATEDIFF(day, d1, d2)

PostgreSQL

NOW(), (d2 - d1)
Pagination

SQL Server

OFFSET n ROWS FETCH NEXT m ROWS ONLY

PostgreSQL

LIMIT m OFFSET n

PostgreSQL LIMIT/OFFSET is simpler

Upsert

SQL Server

MERGE statement

PostgreSQL

INSERT ... ON CONFLICT DO UPDATE

PostgreSQL has no MERGE (until pg 15, partial support)

Square brackets

SQL Server

[table_name], [column_name]

PostgreSQL

table_name or "TableName" for quoted identifiers

Replace [] with double quotes or use lowercase without quotes

@@ROWCOUNT

SQL Server

@@ROWCOUNT

PostgreSQL

GET DIAGNOSTICS v = ROW_COUNT

Step-by-Step Migration Guide

1

Schema Assessment with AWS SCT or pgloader

Use AWS Schema Conversion Tool (SCT) or manually assess the T-SQL schema for migration complexity.

2

Convert DDL

Convert CREATE TABLE statements: IDENTITY → SERIAL, NVARCHAR → VARCHAR, BIT → BOOLEAN, etc. Remove T-SQL-specific table options.

3

Port Stored Procedures to PL/pgSQL

This is the most labor-intensive step. Convert T-SQL procedure syntax to PL/pgSQL.

-- T-SQL:
CREATE PROCEDURE GetUsers @deptId INT AS
BEGIN
  SELECT * FROM users WHERE dept_id = @deptId
END

-- PL/pgSQL:
CREATE OR REPLACE FUNCTION get_users(p_dept_id INT)
RETURNS SETOF users AS $$
BEGIN
  RETURN QUERY SELECT * FROM users WHERE dept_id = p_dept_id;
END;
$$ LANGUAGE plpgsql;
4

Migrate Data

Use AWS DMS for continuous replication or bcp to export data from SQL Server and PostgreSQL COPY command to load it.

-- Export from SQL Server:
bcp mydb.dbo.users out users.csv -S server -U user -P pass -c

-- Import to PostgreSQL:
\COPY users FROM 'users.csv' DELIMITER ',' CSV HEADER;
5

Update Application Layer

Update connection strings, ORM configurations, and any raw SQL queries in the application code. Fix T-SQL-specific syntax.

6

Performance Testing

Run EXPLAIN ANALYZE on critical queries. Add missing indexes. Update table statistics: ANALYZE;

Common Issues & Solutions

MERGE statement has no direct PostgreSQL equivalent (pre-pg15)

Replace MERGE with INSERT ... ON CONFLICT DO UPDATE for upserts. For complex merge logic, use CTEs with multiple DML statements.

T-SQL variables (@var) vs PL/pgSQL variables

Remove @ prefix and DECLARE separately: T-SQL: DECLARE @count INT = 0 → PL/pgSQL: DECLARE count INTEGER := 0;

SQL Server cursors

Most cursor logic can be replaced with set-based SQL operations. If necessary, PostgreSQL supports cursors in PL/pgSQL but they work differently.

ROWVERSION / TIMESTAMP for optimistic concurrency

Replace with a trigger-maintained integer version column or use PostgreSQL's built-in optimistic locking via application-level version checks.

Recommended Migration Tools

AWS Schema Conversion Tool (SCT)

FREE

Automated T-SQL to PL/pgSQL conversion with migration complexity scoring.

AWS Database Migration Service

Continuous replication for zero-downtime migration.

pgAdmin

FREE

PostgreSQL GUI for schema management.

Babelfish for Aurora PostgreSQL

Amazon's compatibility layer that allows SQL Server apps to run on PostgreSQL with minimal changes.

Frequently Asked Questions

How do I convert T-SQL stored procedures to PostgreSQL PL/pgSQL?
Key syntax changes: 1) CREATE PROCEDURE → CREATE OR REPLACE FUNCTION with RETURNS type. 2) @params → parameter list in function signature. 3) DECLARE @var TYPE → DECLARE var TYPE. 4) SET @var → var := value. 5) IF...ELSE stays similar. 6) WHILE stays similar. 7) PRINT → RAISE NOTICE. 8) RETURN with result set → RETURN QUERY or RETURNS SETOF type.
Does PostgreSQL support the MERGE statement?
PostgreSQL 15 added partial MERGE support. For earlier versions, replace MERGE with: INSERT ... ON CONFLICT DO UPDATE SET ... (for upserts), or use CTEs: WITH upsert AS (UPDATE t SET col=val WHERE key=k RETURNING *) INSERT INTO t SELECT val WHERE NOT EXISTS (SELECT 1 FROM upsert).

Ready to Migrate?

Test your PostgreSQL queries in our free SQL Playground before migrating.

Test in Playground All Migration Guides