Migrating T-SQL to PostgreSQL: The Complete Compatibility Guide

Why Organisations Migrate from SQL Server to PostgreSQL

The shift from SQL Server to PostgreSQL is driven by cost (PostgreSQL is open-source), cloud-native compatibility, and the growing ecosystem of PostgreSQL-compatible services (Aurora, Supabase, Neon, CockroachDB). However, T-SQL and PostgreSQL are not syntactically equivalent — and the differences are subtle enough to cause silent data errors if not handled carefully.

The 12 Critical Differences

1. TOP vs LIMIT

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

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

2. ISNULL vs COALESCE

T-SQL's ISNULL accepts exactly two arguments. PostgreSQL uses COALESCE, which accepts any number.

-- T-SQL
SELECT ISNULL(discount, 0) FROM orders;

-- PostgreSQL
SELECT COALESCE(discount, 0) FROM orders;

3. String Concatenation

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

-- PostgreSQL
SELECT first_name || ' ' || last_name AS full_name FROM customers;
-- Or: CONCAT(first_name, ' ', last_name)

4. GETDATE() vs NOW()

-- T-SQL
SELECT GETDATE();

-- PostgreSQL
SELECT NOW();
-- Or: CURRENT_TIMESTAMP

5. DATEADD vs Interval Arithmetic

-- T-SQL
SELECT DATEADD(day, 30, created_at) FROM orders;

-- PostgreSQL
SELECT created_at + INTERVAL '30 days' FROM orders;

6. DATEDIFF

-- T-SQL
SELECT DATEDIFF(day, start_date, end_date) AS days_diff FROM projects;

-- PostgreSQL
SELECT (end_date - start_date) AS days_diff FROM projects;
-- For other units: EXTRACT(EPOCH FROM (end_date - start_date)) / 86400

7. Identity Columns

-- T-SQL
CREATE TABLE orders (
  id INT IDENTITY(1,1) PRIMARY KEY
);

-- PostgreSQL
CREATE TABLE orders (
  id SERIAL PRIMARY KEY
  -- Or: id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

8. VARCHAR(MAX) vs TEXT

-- T-SQL
description VARCHAR(MAX)

-- PostgreSQL
description TEXT

9. Temporary Tables

-- T-SQL
SELECT * INTO #temp_orders FROM orders WHERE status = 'pending';

-- PostgreSQL
CREATE TEMP TABLE temp_orders AS
SELECT * FROM orders WHERE status = 'pending';

10. Schema Differences

T-SQL defaults to dbo schema; PostgreSQL defaults to public. Update all unqualified object references.

11. NOLOCK Hints

PostgreSQL does not support WITH (NOLOCK). Remove all lock hints — PostgreSQL's MVCC architecture provides non-blocking reads by default.

-- T-SQL
SELECT * FROM orders WITH (NOLOCK) WHERE status = 'completed';

-- PostgreSQL — simply remove the hint
SELECT * FROM orders WHERE status = 'completed';

12. Stored Procedure Syntax

T-SQL stored procedures use CREATE PROCEDURE with AS BEGIN...END. PostgreSQL uses CREATE FUNCTION with LANGUAGE plpgsql and $$...$$ delimiters.

Automating the Migration

Manual migration of large codebases is error-prone and time-consuming. SQL Querywise Converter handles all 12 transformations automatically, plus 40+ additional dialect-specific patterns, and generates a compatibility report highlighting any constructs that require manual review.

*SQL Querywise Converter migrates T-SQL to PostgreSQL (and 5 other dialects) automatically, with a full incompatibility report.*