---
# === IDENTITY ===
id: software/migrations/sqlserver-to-postgresql/2026
canonical_question: "How do I migrate from SQL Server to PostgreSQL?"
aliases:
  - "SQL Server to PostgreSQL migration"
  - "MSSQL to PostgreSQL"
  - "convert SQL Server to Postgres"
  - "SQL Server to Postgres guide"
  - "T-SQL to PL/pgSQL conversion"
  - "migrate database from SQL Server to PostgreSQL"
  - "SQL Server to PostgreSQL data type mapping"
  - "MSSQL to Postgres stored procedure migration"
entity_type: software_reference
domain: software > migrations > sqlserver_to_postgresql
region: global
jurisdiction: global
temporal_scope: 2020-2026

# === VERIFICATION ===
last_verified: 2026-05-17
confidence: 0.93
freshness: evolving
version: 2.1
first_published: 2026-02-17

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: evolving
  last_breaking_change: "PostgreSQL 18 (2025-09-25) — OLD/NEW in RETURNING (incl. MERGE), temporal constraints, virtual generated columns, async I/O, uuidv7(), OAuth"
  next_review: 2026-11-13
  change_sensitivity: medium

# === CONSTRAINTS ===
constraints:
  - "Always reset sequences after bulk data import — run setval() for every SERIAL/IDENTITY column or inserts will fail with duplicate key errors"
  - "PostgreSQL 15+ required for MERGE statement; PostgreSQL 17+ adds WHEN NOT MATCHED BY SOURCE; PostgreSQL 18+ adds OLD/NEW values in MERGE RETURNING; for PG 13-14 use INSERT ... ON CONFLICT instead"
  - "Never mix jQuery-style dbo. schema qualifiers in PostgreSQL — map dbo to public or create equivalent schemas before data migration"
  - "CLR stored procedures (.NET code in SQL Server) have no PostgreSQL equivalent — must be rewritten as PL/pgSQL, PL/Python, or moved to application layer"
  - "Azure Database Migration Service does NOT support SQL Server to PostgreSQL — use AWS DMS, pgloader, or manual migration instead"
  - "SQL Server NVARCHAR (UTF-16) maps to TEXT (UTF-8) — character length semantics differ, test all string length validations after migration"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "User wants to migrate MySQL to PostgreSQL (not SQL Server)"
    use_instead: "software/migrations/mysql-to-postgresql/2026"
  - condition: "User wants to migrate Oracle to PostgreSQL"
    use_instead: "software/migrations/oracle-to-postgresql/2026"
  - condition: "User wants to stay on SQL Server but upgrade versions"
    use_instead: "software/debugging/sqlserver-upgrade/2026"

# === AGENT HINTS ===
inputs_needed:
  - key: database_size
    question: "What is the approximate size of the SQL Server database?"
    type: choice
    options: ["Small (<10 GB)", "Medium (10-500 GB)", "Large (>500 GB)"]
  - key: tsql_complexity
    question: "How many T-SQL stored procedures and functions need to be converted?"
    type: choice
    options: ["Few (<20)", "Moderate (20-100)", "Many (>100, including CLR)"]
  - key: downtime_tolerance
    question: "How much downtime is acceptable during the migration cutover?"
    type: choice
    options: ["Hours (batch cutover)", "Minutes (near-zero downtime)", "Zero (continuous replication required)"]

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/software/migrations/sqlserver-to-postgresql/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-05-17)"

# === RELATED UNITS ===
related_kos:
  related_to:
    - id: "software/migrations/mysql-to-postgresql/2026"
      label: "MySQL to PostgreSQL Migration"
    - id: "software/debugging/postgresql-slow-queries/2026"
      label: "Diagnosing slow PostgreSQL queries"
  solves:
    - id: "software/patterns/database-migration-strategies/2026"
      label: "Database Migration Strategies"
  alternative_to:
    - id: "software/migrations/oracle-to-postgresql/2026"
      label: "Oracle to PostgreSQL Migration"
  often_confused_with:
    - id: "software/migrations/mongodb-to-postgresql/2026"
      label: "MongoDB to PostgreSQL Migration"

# === SOURCES (9 authoritative sources) ===
# Types: official_docs, technical_blog, rfc_spec, academic_paper, community_resource, industry_report
# Reliability: high, moderate_high, moderate, moderate_low, low, authoritative
sources:
  - id: src1
    title: "Converting from other Databases to PostgreSQL"
    author: PostgreSQL Wiki
    url: https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL
    type: official_docs
    published: 2024-06-01
    reliability: authoritative
  - id: src2
    title: "SQL Server to Aurora PostgreSQL Migration Playbook — Data Types"
    author: AWS
    url: https://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/chap-sql-server-aurora-pg.sql.datatypes.html
    type: official_docs
    published: 2025-01-15
    reliability: authoritative
  - id: src3
    title: "MS SQL to Postgres — pgloader 3.6.9 documentation"
    author: pgloader
    url: https://pgloader.readthedocs.io/en/latest/ref/mssql.html
    type: official_docs
    published: 2024-03-01
    reliability: high
  - id: src4
    title: "Stored procedures for T-SQL — SQL Server to Aurora PostgreSQL Migration Playbook"
    author: AWS
    url: https://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/chap-sql-server-aurora-pg.tsql.storedprocedures.html
    type: official_docs
    published: 2025-01-15
    reliability: authoritative
  - id: src5
    title: "Microsoft SQL Server to PostgreSQL Migration by Ian Harding"
    author: PostgreSQL Wiki
    url: https://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding
    type: community_resource
    published: 2023-08-10
    reliability: high
  - id: src6
    title: "sqlserver2pgsql — Migration tool to convert SQL Server Database to PostgreSQL"
    author: Dalibo
    url: https://dalibo.github.io/sqlserver2pgsql/
    type: community_resource
    published: 2024-01-20
    reliability: moderate_high
  - id: src7
    title: "Migrate SQL Server to Amazon Aurora PostgreSQL using best practices and lessons learned"
    author: AWS Database Blog
    url: https://aws.amazon.com/blogs/database/migrate-sql-server-to-amazon-aurora-postgresql-using-best-practices-and-lessons-learned-from-the-field/
    type: technical_blog
    published: 2024-09-15
    reliability: high
  - id: src8
    title: "PostgreSQL 17 Release Notes"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/17/release-17.html
    type: official_docs
    published: 2024-09-26
    reliability: authoritative
  - id: src9
    title: "PostgreSQL 18 Released! — OLD/NEW in RETURNING, temporal constraints, virtual generated columns, async I/O, uuidv7(), OAuth"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/about/news/postgresql-18-released-3142/
    type: official_docs
    published: 2025-09-25
    reliability: authoritative
---

# How to Migrate from SQL Server to PostgreSQL

## TL;DR

- **Bottom line**: Migrate schema first (convert data types, rewrite T-SQL to PL/pgSQL), then migrate data using pgloader or AWS DMS, and finally update application connection strings and SQL syntax.
- **Key tool/command**: `pgloader mssql://user:pass@host/dbname postgresql://user:pass@host/dbname`
- **Watch out for**: Case sensitivity — SQL Server is case-insensitive by default for identifiers, PostgreSQL lowercases unquoted identifiers and is case-sensitive for quoted ones.
- **Works with**: SQL Server 2012-2022, PostgreSQL 13-18, pgloader 3.6+, AWS DMS, AWS SCT. PostgreSQL 18 (Sept 2025) recommended for best SQL Server feature parity (OLD/NEW in MERGE RETURNING, temporal constraints, async I/O, uuidv7, OAuth, plus PG 17 carry-overs: JSON_TABLE, COPY ON_ERROR). [src9]

## Constraints
<!-- Agents: read this section before recommending any action from this unit.
     These are hard constraints that must not be violated. -->

- Always reset sequences after bulk data import using `setval()` for every SERIAL/IDENTITY column — skipping this causes duplicate key errors on the next INSERT.
- PostgreSQL 15+ required for `MERGE` statement. For PostgreSQL 13-14, rewrite SQL Server MERGE as `INSERT ... ON CONFLICT`. PostgreSQL 17 adds `WHEN NOT MATCHED BY SOURCE` and `RETURNING`; PostgreSQL 18 adds `OLD`/`NEW` value access in MERGE/INSERT/UPDATE/DELETE `RETURNING` clauses for full SQL Server `OUTPUT inserted.*, deleted.*` parity. [src9]
- CLR stored procedures (C#/.NET code in SQL Server) have zero PostgreSQL equivalent — rewrite as PL/pgSQL, PL/Python, or extract logic to the application layer before migration.
- Azure Database Migration Service does NOT support SQL Server to PostgreSQL migrations — use AWS DMS/SCT, pgloader, or manual migration.
- SQL Server `NVARCHAR` (UTF-16) maps to PostgreSQL `TEXT` (UTF-8). Character length semantics differ — always test `LEN()` vs `LENGTH()` comparisons and `CHECK` constraints after migration.
- Never run pgloader against a production SQL Server without testing on a snapshot first — pgloader issues full table locks during schema introspection on some SQL Server versions.

## Quick Reference

| SQL Server Syntax | PostgreSQL Equivalent | Example |
|---|---|---|
| `IDENTITY(1,1)` | `GENERATED ALWAYS AS IDENTITY` or `SERIAL` | `id INTEGER GENERATED ALWAYS AS IDENTITY` |
| `NVARCHAR(MAX)` | `TEXT` | `description TEXT` |
| `DATETIME` / `DATETIME2` | `TIMESTAMP` / `TIMESTAMPTZ` | `created_at TIMESTAMPTZ DEFAULT NOW()` |
| `BIT` | `BOOLEAN` | `is_active BOOLEAN DEFAULT TRUE` |
| `UNIQUEIDENTIFIER` | `UUID` | `id UUID DEFAULT gen_random_uuid()` |
| `MONEY` | `NUMERIC(19,4)` | `price NUMERIC(19,4)` |
| `NTEXT` / `NCHAR` | `TEXT` / `CHAR` | `notes TEXT` |
| `GETDATE()` | `NOW()` or `CURRENT_TIMESTAMP` | `SELECT NOW()` |
| `ISNULL(expr, alt)` | `COALESCE(expr, alt)` | `COALESCE(name, 'N/A')` |
| `TOP N` | `LIMIT N` | `SELECT * FROM t LIMIT 10` |
| `SCOPE_IDENTITY()` | `RETURNING id` or `currval()` | `INSERT INTO t (name) VALUES ('x') RETURNING id` |
| `CHARINDEX(sub, str)` | `POSITION(sub IN str)` | `POSITION('abc' IN column_name)` |
| `LEN(str)` | `LENGTH(str)` | `LENGTH(column_name)` |
| `CONVERT(type, expr)` | `CAST(expr AS type)` or `::type` | `created_at::DATE` |
| `[bracketed identifiers]` | `"quoted identifiers"` | `SELECT "Column Name" FROM "My Table"` |
| `DATEADD(day, N, date)` | `date + INTERVAL 'N days'` | `NOW() + INTERVAL '30 days'` |
| `DATEDIFF(day, d1, d2)` | `d2 - d1` or `EXTRACT(EPOCH FROM ...)` | `end_date - start_date` |
| `OPENJSON()` | `JSON_TABLE()` (PG 17+) or `jsonb_to_recordset()` | `SELECT * FROM JSON_TABLE(data, '$.items[*]' ...)` |

## Decision Tree

```
START
├── Database size < 10 GB and simple schema (few stored procedures)?
│   ├── YES → Use pgloader for one-command migration (see Step 3)
│   └── NO ↓
├── Heavy T-SQL stored procedures, triggers, CLR assemblies?
│   ├── YES → Use AWS SCT for schema conversion + manual PL/pgSQL rewrite
│   └── NO ↓
├── Need near-zero downtime (production migration)?
│   ├── YES → Use AWS DMS with CDC (Change Data Capture) for continuous replication
│   └── NO ↓
├── Migrating to AWS (Aurora PostgreSQL, RDS)?
│   ├── YES → Use AWS SCT + DMS combo (schema conversion + data replication)
│   └── NO ↓
├── Schema only (data migrated separately)?
│   ├── YES → Use sqlserver2pgsql to generate DDL scripts, then bulk-load data
│   └── NO ↓
└── DEFAULT → pgloader for schema + data, then manually convert stored procedures
```

## Step-by-Step Guide

### 1. Audit the SQL Server database

Inventory all objects that need migration: tables, views, stored procedures, triggers, functions, indexes, and constraints. Quantify T-SQL complexity to estimate manual conversion effort. [src5]

```sql
-- Count objects by type in SQL Server
SELECT type_desc, COUNT(*) AS object_count
FROM sys.objects
WHERE is_ms_shipped = 0
GROUP BY type_desc
ORDER BY object_count DESC;

-- List all stored procedures with line counts
SELECT s.name AS schema_name, p.name AS proc_name,
       LEN(m.definition) - LEN(REPLACE(m.definition, CHAR(10), '')) AS line_count
FROM sys.procedures p
JOIN sys.schemas s ON p.schema_id = s.schema_id
JOIN sys.sql_modules m ON p.object_id = m.object_id
ORDER BY line_count DESC;

-- Check database size
EXEC sp_spaceused;

-- Identify CLR assemblies (these need full rewrite)
SELECT a.name AS assembly_name, a.permission_set_desc,
       m.assembly_class, m.assembly_method
FROM sys.assemblies a
LEFT JOIN sys.assembly_modules m ON a.assembly_id = m.assembly_id
WHERE a.is_user_defined = 1;
```

**Verify**: Review the object counts — note the number of stored procedures (these need manual review), CLR assemblies (these need full rewrite), and total database size (determines migration tool choice).

### 2. Convert the schema

Export the SQL Server schema and convert data types, constraints, and indexes to PostgreSQL syntax. Use sqlserver2pgsql or AWS SCT for automated conversion, then review the output. [src2, src6]

```bash
# Option A: sqlserver2pgsql (generates DDL scripts from SQL dump)
# First, generate a SQL Server schema dump
sqlcmd -S localhost -d mydb -E -Q "SET NOCOUNT ON" -o schema_dump.sql

# Convert to PostgreSQL DDL
perl sqlserver2pgsql.pl -f schema_dump.sql \
  -b before_migration.sql \
  -a after_migration.sql \
  -u unsure_migration.sql

# Review and apply the before script (tables, types, columns)
psql -h pghost -d targetdb -f before_migration.sql

# Option B: AWS SCT (GUI tool — generates conversion report)
# Download AWS Schema Conversion Tool, connect to both databases,
# review the conversion assessment, then apply converted schema
```

**Verify**: `psql -d targetdb -c "\dt"` shows all expected tables. Compare table count against SQL Server: `SELECT COUNT(*) FROM information_schema.tables WHERE table_type = 'BASE TABLE'`.

### 3. Migrate the data

Load data from SQL Server into PostgreSQL. pgloader handles schema + data in a single command, including data type casting and error handling. pgloader can handle up to 3 TB/hour when properly tuned. [src3]

```bash
# Option A: pgloader (recommended for most migrations)
pgloader mssql://sa:password@sqlserver-host/sourcedb \
         postgresql://pguser:pgpass@pg-host/targetdb

# pgloader with a load file for more control
cat > migration.load << 'EOF'
LOAD DATABASE
  FROM mssql://sa:password@sqlserver-host/sourcedb
  INTO postgresql://pguser:pgpass@pg-host/targetdb

WITH include drop, create tables, create indexes,
     reset sequences, downcase identifiers

SET work_mem to '512MB', maintenance_work_mem to '1GB'

CAST type datetime to timestamptz using mssql-datetime-to-timestamptz,
     type nvarchar to text,
     type ntext to text,
     type money to numeric using mssql-money-to-numeric,
     type uniqueidentifier to uuid using mssql-uniqueidentifier-to-uuid

EXCLUDING TABLE NAMES MATCHING 'sysdiagrams', 'dtproperties'
;
EOF

pgloader migration.load

# Option B: AWS DMS (for large databases or continuous replication)
# Create DMS replication instance, source/target endpoints, and migration task
# via AWS Console or CLI — supports CDC for near-zero downtime cutover
```

**Verify**: Compare row counts across all tables:
```sql
-- On SQL Server:
SELECT t.name, p.rows FROM sys.tables t
JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1) ORDER BY t.name;

-- On PostgreSQL:
SELECT relname, n_tup_ins - n_tup_del AS row_count
FROM pg_stat_user_tables ORDER BY relname;
```

### 4. Convert stored procedures and functions

Rewrite T-SQL stored procedures as PL/pgSQL functions. This is the most manual part — automated tools handle 60-80% of cases, the rest needs manual conversion. [src4]

```sql
-- SQL Server T-SQL stored procedure
CREATE PROCEDURE dbo.GetActiveUsers
    @MinAge INT = 18,
    @Status NVARCHAR(20) = 'active'
AS
BEGIN
    SET NOCOUNT ON;
    SELECT u.user_id, u.name, u.email
    FROM dbo.users u
    WHERE u.age >= @MinAge
      AND u.status = @Status
    ORDER BY u.name;
END;

-- PostgreSQL PL/pgSQL equivalent
CREATE OR REPLACE FUNCTION get_active_users(
    p_min_age INTEGER DEFAULT 18,
    p_status TEXT DEFAULT 'active'
)
RETURNS TABLE(user_id INTEGER, name TEXT, email TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT u.user_id, u.name, u.email
    FROM users u
    WHERE u.age >= p_min_age
      AND u.status = p_status
    ORDER BY u.name;
END;
$$;

-- Call: SELECT * FROM get_active_users(21, 'active');
```

**Verify**: Call each converted function and compare output against the SQL Server procedure: `SELECT * FROM get_active_users()` should return the same rows.

### 5. Convert T-SQL syntax in application code

Update SQL queries embedded in your application code. Replace SQL Server-specific syntax with PostgreSQL equivalents. [src2, src5]

```sql
-- SQL Server:
SELECT TOP 10 u.name, ISNULL(u.nickname, u.name) AS display_name,
       CONVERT(VARCHAR, u.created_at, 120) AS created_date
FROM [dbo].[users] u WITH (NOLOCK)
WHERE u.status = 'active'
ORDER BY u.created_at DESC;

-- PostgreSQL:
SELECT u.name, COALESCE(u.nickname, u.name) AS display_name,
       TO_CHAR(u.created_at, 'YYYY-MM-DD HH24:MI:SS') AS created_date
FROM users u
WHERE u.status = 'active'
ORDER BY u.created_at DESC
LIMIT 10;
```

**Verify**: Run your application's test suite against PostgreSQL. Check for syntax errors in logs: `grep -i 'error\|syntax' app.log`.

### 6. Update connection strings and drivers

Replace SQL Server drivers with PostgreSQL drivers in your application configuration. [src1]

```bash
# Node.js: replace mssql with pg
npm uninstall mssql
npm install pg
# Connection: const pool = new Pool({ connectionString: 'postgresql://user:pass@host:5432/db' })

# Python: replace pyodbc/pymssql with psycopg
pip uninstall pyodbc
pip install psycopg[binary]
# Connection: conn = psycopg.connect("postgresql://user:pass@host:5432/db")

# .NET: replace System.Data.SqlClient with Npgsql
dotnet remove package System.Data.SqlClient
dotnet add package Npgsql
# Connection: new NpgsqlConnection("Host=host;Database=db;Username=user;Password=pass")

# Java: replace jtds/mssql-jdbc with postgresql
# Maven: replace com.microsoft.sqlserver:mssql-jdbc with org.postgresql:postgresql
# JDBC: jdbc:postgresql://host:5432/db
```

**Verify**: Application connects successfully. Run: `SELECT version();` through your app to confirm PostgreSQL connectivity.

### 7. Apply indexes and constraints

Run the deferred indexes and constraints (the "after" script if using sqlserver2pgsql, or create manually). [src6]

```sql
-- Apply indexes (run after data migration for speed)
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_status ON users (status) WHERE status = 'active';  -- partial index (PostgreSQL-specific)
CREATE UNIQUE INDEX idx_users_username ON users (LOWER(username));  -- functional index

-- Apply foreign key constraints
ALTER TABLE orders ADD CONSTRAINT fk_orders_users
    FOREIGN KEY (user_id) REFERENCES users (user_id);

-- Reset sequences after data import (critical if using SERIAL/IDENTITY)
SELECT setval('users_user_id_seq', (SELECT MAX(user_id) FROM users));
SELECT setval('orders_order_id_seq', (SELECT MAX(order_id) FROM orders));
```

**Verify**: `SELECT indexname FROM pg_indexes WHERE schemaname = 'public' ORDER BY indexname;` lists all expected indexes. Test foreign key enforcement: `INSERT INTO orders (user_id) VALUES (999999)` should fail.

### 8. Validate and run parallel operations

After migration, run both databases in parallel during a validation period. Compare query results and performance before final cutover. [src7]

```bash
# Run a query comparison script
# For each critical query, execute on both databases and diff results
psql -d pgdb -t -A -c "SELECT COUNT(*) FROM users WHERE status='active'" > pg_count.txt
sqlcmd -S sqlhost -d mydb -h -1 -Q "SELECT COUNT(*) FROM users WHERE status='active'" > mssql_count.txt
diff pg_count.txt mssql_count.txt

# Performance baseline comparison
psql -d pgdb -c "EXPLAIN ANALYZE SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days';"
```

**Verify**: All critical query results match. PostgreSQL query performance is within acceptable range of SQL Server baseline.

## Code Examples

### Python/psycopg: Migrating a SQL Server data access layer to PostgreSQL

> Full script: [python-psycopg-migrating-a-sql-server-data-access-.py](scripts/python-psycopg-migrating-a-sql-server-data-access-.py) (47 lines)

```python
# Input:  Python app using pyodbc for SQL Server
# Output: Same app using psycopg for PostgreSQL
import psycopg
from contextlib import contextmanager
# BEFORE: SQL Server with pyodbc
# ... (see full script)
```

### Node.js/pg: Converting Express routes from mssql to pg

> Full script: [node-js-pg-converting-express-routes-from-mssql-to.js](scripts/node-js-pg-converting-express-routes-from-mssql-to.js) (57 lines)

```javascript
// Input:  Express app using mssql package for SQL Server
// Output: Same routes using pg package for PostgreSQL
import pg from 'pg';
const { Pool } = pg;
// BEFORE: SQL Server
# ... (see full script)
```

### SQL: Common T-SQL to PL/pgSQL conversions

> Full script: [sql-common-t-sql-to-pl-pgsql-conversions.sql](scripts/sql-common-t-sql-to-pl-pgsql-conversions.sql) (54 lines)

```sql
-- Input:  T-SQL patterns commonly found in SQL Server applications
-- Output: PostgreSQL PL/pgSQL equivalents
-- 1. Temporary tables: # prefix → CREATE TEMP TABLE
-- SQL Server:  SELECT * INTO #temp_users FROM users WHERE active = 1
-- PostgreSQL:
# ... (see full script)
```

### C#/.NET: Migrating from SqlClient to Npgsql

```csharp
// Input:  .NET app using System.Data.SqlClient
// Output: Same app using Npgsql for PostgreSQL
using Npgsql;

// BEFORE: var conn = new SqlConnection("Server=sqlhost;Database=mydb;...");
// AFTER:
await using var conn = new NpgsqlConnection("Host=pghost;Database=mydb;Username=pguser;Password=pgpass");
await conn.OpenAsync();

// @param → $1 positional, or use named params with NpgsqlParameter
await using var cmd = new NpgsqlCommand(
    "SELECT * FROM users WHERE status = $1 ORDER BY created_at DESC LIMIT $2", conn);
cmd.Parameters.AddWithValue(NpgsqlTypes.NpgsqlDbType.Text, "active");
cmd.Parameters.AddWithValue(NpgsqlTypes.NpgsqlDbType.Integer, 10);
await using var reader = await cmd.ExecuteReaderAsync();
```

## Anti-Patterns

### Wrong: Using square brackets for identifiers in PostgreSQL

```sql
-- ❌ BAD — SQL Server bracket syntax doesn't work in PostgreSQL
SELECT [user_id], [first name], [Order Date]
FROM [dbo].[users]
WHERE [status] = 'active';
```

### Correct: Use double quotes for identifiers (or avoid quoting by using lowercase)

```sql
-- ✅ GOOD — PostgreSQL uses double quotes; prefer lowercase unquoted identifiers
SELECT user_id, "first name", "Order Date"
FROM users
WHERE status = 'active';

-- BEST — rename columns to avoid quoting entirely
ALTER TABLE users RENAME COLUMN "first name" TO first_name;
ALTER TABLE users RENAME COLUMN "Order Date" TO order_date;
```

### Wrong: Using TOP N instead of LIMIT

```sql
-- ❌ BAD — TOP is SQL Server syntax, not supported in PostgreSQL
SELECT TOP 10 * FROM orders ORDER BY created_at DESC;
```

### Correct: Use LIMIT for row limiting

```sql
-- ✅ GOOD — PostgreSQL uses LIMIT (SQL standard also supports FETCH FIRST)
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- Or: SELECT * FROM orders ORDER BY created_at DESC FETCH FIRST 10 ROWS ONLY;
```

### Wrong: Using ISNULL() for null coalescing

```sql
-- ❌ BAD — ISNULL is SQL Server-specific and doesn't exist in PostgreSQL
SELECT ISNULL(nickname, name) AS display_name FROM users;
```

### Correct: Use COALESCE (SQL standard, works everywhere)

```sql
-- ✅ GOOD — COALESCE is ANSI SQL, portable across all databases
SELECT COALESCE(nickname, name) AS display_name FROM users;
```

### Wrong: Using NOLOCK hints in PostgreSQL

```sql
-- ❌ BAD — Table hints like NOLOCK are SQL Server-specific
SELECT * FROM orders WITH (NOLOCK) WHERE status = 'pending';
```

### Correct: Use PostgreSQL's MVCC (no dirty reads by default)

```sql
-- ✅ GOOD — PostgreSQL uses MVCC; readers never block writers
-- No hint needed — default READ COMMITTED isolation prevents dirty reads
SELECT * FROM orders WHERE status = 'pending';
-- If you need weaker isolation: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- (Note: PostgreSQL treats READ UNCOMMITTED as READ COMMITTED)
```

### Wrong: Using += for string concatenation or GETDATE() for current time

```sql
-- ❌ BAD — SQL Server string concatenation and date functions
DECLARE @fullname NVARCHAR(200);
SET @fullname = @first + ' ' + @last;
SELECT * FROM events WHERE event_date > GETDATE();
```

### Correct: Use || for concatenation and NOW() for current time

```sql
-- ✅ GOOD — PostgreSQL standard syntax
-- In PL/pgSQL:
DECLARE
    v_fullname TEXT;
BEGIN
    v_fullname := v_first || ' ' || v_last;
END;

-- In queries:
SELECT * FROM events WHERE event_date > NOW();
```

### Wrong: Using OPENJSON() in PostgreSQL (pre-17)

```sql
-- ❌ BAD — OPENJSON is SQL Server-specific; JSON_TABLE requires PG 17+
SELECT * FROM OPENJSON(@json_data);
```

### Correct: Use jsonb functions or JSON_TABLE (PG 17+)

```sql
-- ✅ GOOD (PG 13+) — Use jsonb_to_recordset for JSON array to table
SELECT * FROM jsonb_to_recordset('[ {"id":1,"name":"Alice"} ]'::jsonb)
    AS t(id int, name text);

-- ✅ GOOD (PG 17+) — Use SQL/JSON JSON_TABLE for full OPENJSON parity
SELECT * FROM JSON_TABLE(
    '{"users":[{"id":1,"name":"Alice"}]}'::jsonb,
    '$.users[*]' COLUMNS (id int, name text)
);
```

## Common Pitfalls

- **Case sensitivity mismatch**: SQL Server is case-insensitive for identifiers by default; PostgreSQL lowercases unquoted identifiers and is case-sensitive for quoted ones. Fix: Use `downcase identifiers` in pgloader, or `CITEXT` extension for case-insensitive text columns. [src2]
- **BIT vs BOOLEAN**: SQL Server `BIT` stores 0/1, PostgreSQL `BOOLEAN` uses true/false. Inserting `1` into a BOOLEAN column fails. Fix: Cast during migration: `CASE WHEN bit_col = 1 THEN TRUE ELSE FALSE END`, or use pgloader's type casting. [src2]
- **IDENTITY vs SERIAL sequence ownership**: After bulk data import, PostgreSQL sequences are not auto-advanced. Fix: Run `SELECT setval('table_column_seq', (SELECT MAX(column) FROM table))` for every serial/identity column after data load. [src3]
- **Stored procedure return semantics**: T-SQL procedures implicitly return result sets from the last SELECT; PL/pgSQL requires explicit `RETURNS TABLE` or `RETURNS SETOF` declarations. Fix: Rewrite procedures as functions with `RETURN QUERY SELECT ...`. [src4]
- **Transaction isolation differences**: SQL Server default is READ COMMITTED with row locking; PostgreSQL uses MVCC. `WITH (NOLOCK)` hints must be removed — they do nothing in PostgreSQL. Fix: Remove all table hints; rely on PostgreSQL's MVCC for non-blocking reads. [src5]
- **String concatenation with NULL**: In SQL Server, `'hello' + NULL` returns `NULL` (by default). In PostgreSQL, `'hello' || NULL` also returns `NULL`. But `CONCAT()` in SQL Server ignores NULLs while PostgreSQL's `CONCAT()` also ignores NULLs. Fix: Use `CONCAT()` or `COALESCE()` explicitly for null-safe concatenation. [src1]
- **Schema-qualified object names**: SQL Server uses `dbo.tablename`, PostgreSQL uses `public.tablename`. Fix: Map `dbo` to `public` in your migration tool, or create equivalent schemas. [src7]
- **Date arithmetic syntax**: SQL Server uses `DATEADD(unit, N, date)` and `DATEDIFF(unit, d1, d2)`. PostgreSQL uses interval arithmetic: `date + INTERVAL 'N days'`. Fix: Replace all DATEADD/DATEDIFF calls with PostgreSQL interval syntax. [src2]
- **COPY ON_ERROR (PG 17+)**: When bulk-loading data, PostgreSQL 17 added `ON_ERROR ignore` to COPY, allowing skip of malformed rows. On PG 13-16, a single bad row aborts the entire COPY. Fix: Pre-validate data or use pgloader which handles errors natively. [src8]

## Diagnostic Commands

```bash
# Check PostgreSQL version and connection
psql -c "SELECT version();"

# Compare table counts between SQL Server and PostgreSQL
# SQL Server:
sqlcmd -S sqlhost -d mydb -Q "SELECT COUNT(*) FROM information_schema.tables WHERE table_type='BASE TABLE'"
# PostgreSQL:
psql -d mydb -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'"

# Compare row counts for a specific table
psql -d mydb -c "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY relname;"

# Check for sequence drift after migration (IDENTITY/SERIAL)
psql -d mydb -c "SELECT sequencename, last_value FROM pg_sequences WHERE schemaname = 'public';"

# Find invalid/failed objects after schema conversion
psql -d mydb -c "SELECT proname, prosrc FROM pg_proc WHERE prosrc LIKE '%ERROR%' OR prosrc LIKE '%TODO%';"

# Verify foreign key constraints are in place
psql -d mydb -c "SELECT conname, conrelid::regclass, confrelid::regclass FROM pg_constraint WHERE contype = 'f';"

# Test connection from application driver
python -c "import psycopg; print(psycopg.connect('postgresql://user:pass@host/db').execute('SELECT 1').fetchone())"

# Check for missing indexes after migration
psql -d mydb -c "SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE seq_scan > 1000 AND idx_scan < 10 ORDER BY seq_scan DESC;"
```

## Version History & Compatibility

| Version | Status | Key Features for Migration | Migration Notes |
|---|---|---|---|
| PostgreSQL 18 (Sept 2025) | Current | OLD/NEW in RETURNING (incl. MERGE), temporal constraints (`PERIOD`, `WITHOUT OVERLAPS`), virtual generated columns, asynchronous I/O (`io_method`), uuidv7(), OAuth 2.0 auth, skip-scan B-tree | Best migration target — full SQL Server `OUTPUT inserted/deleted` parity via OLD/NEW, plus AIO gives up to 3× sequential-scan throughput |
| PostgreSQL 17 (2024) | Mainstream | JSON_TABLE, MERGE RETURNING, WHEN NOT MATCHED BY SOURCE, COPY ON_ERROR ignore, incremental backup | Good target if PG 18 not yet available in your managed service; use `GENERATED ALWAYS AS IDENTITY` |
| PostgreSQL 16 (2023) | Supported | Logical replication from standby, improved COPY perf | Acceptable target; improved bulk load performance |
| PostgreSQL 15 (2022) | Supported | MERGE statement added, SECURITY INVOKER views | SQL Server MERGE can be ported directly (basic form) |
| PostgreSQL 14 (2021) | Maintenance | Multirange types, RETURNING improvements | `RETURNING` improvements help with IDENTITY migration |
| PostgreSQL 13 (2020) | EOL Nov 2025 | Parallel vacuum, deduplicated B-tree | No longer supported — upgrade to 16+ before migrating |
| SQL Server 2022 | Current | Ledger tables, enhanced query store | Latest source version — full DMS/pgloader support |
| SQL Server 2019 | Mainstream | UTF-8 collation support | UTF-8 collation simplifies text migration to PostgreSQL |
| SQL Server 2016-2017 | Extended | JSON support, graph tables | JSON columns should migrate to JSONB in PostgreSQL |

## When to Use / When Not to Use

| Use When | Don't Use When | Use Instead |
|---|---|---|
| Moving to open-source to reduce licensing costs (60-90% savings) | You rely on SQL Server-specific features (SSRS, SSAS, SSIS) | Keep SQL Server or migrate BI tools separately |
| Cloud migration to AWS/GCP (Aurora PostgreSQL, Cloud SQL) | Database is < 100 MB and rarely changes | Export to SQLite or CSV |
| Need advanced features (JSONB, full-text search, PostGIS) | Team has no PostgreSQL experience and tight deadline | Azure SQL Managed Instance (lift-and-shift) |
| Application uses standard SQL with minimal T-SQL | Hundreds of complex stored procedures with CLR assemblies | Hybrid approach: migrate tables, keep proc logic in app layer |
| Linux/container deployment | Windows-only infrastructure with no plans to change | SQL Server on Linux or Azure SQL |
| Need JSON_TABLE, advanced partitioning, or extensions | Regulatory requirement to use specific vendor DB | Stay on SQL Server or evaluate alternatives |

## Important Caveats

- PostgreSQL `IDENTITY` columns (recommended over `SERIAL`) require PostgreSQL 10+. Always reset sequences after bulk data import using `setval()`.
- T-SQL `@@IDENTITY` and `SCOPE_IDENTITY()` have no direct equivalent — use `INSERT ... RETURNING id` or `currval('sequence_name')` instead.
- SQL Server's `MERGE` statement is only fully supported in PostgreSQL 17+ (with `WHEN NOT MATCHED BY SOURCE` and `RETURNING`). PostgreSQL 15-16 support basic MERGE. For earlier versions, use `INSERT ... ON CONFLICT`.
- PostgreSQL does not support cross-database queries (`USE database`). Each connection targets one database. Use schemas or foreign data wrappers for cross-database access.
- CLR stored procedures (C#/.NET code in SQL Server) have no PostgreSQL equivalent. Rewrite as PL/pgSQL, PL/Python, or move logic to the application layer.
- SQL Server's `NVARCHAR` (UTF-16) maps to `TEXT` (UTF-8) in PostgreSQL. Character length semantics differ — test string length validations after migration.
- PostgreSQL 17's `COPY ... ON_ERROR ignore` option finally matches SQL Server's ability to skip bad rows during bulk import — use PG 17+ for the smoothest data migration experience.
- PostgreSQL 18 (released 2025-09-25) adds `OLD` / `NEW` value access in `RETURNING` for INSERT, UPDATE, DELETE, and MERGE — the closest equivalent yet to SQL Server's `OUTPUT inserted.*, deleted.*` clause. Example: `UPDATE users SET status='active' WHERE id=$1 RETURNING old.status AS prev_status, new.status AS curr_status;`. This eliminates a common reason teams stayed on SQL Server for audit-trail workloads. [src9]
- PostgreSQL 18 also introduces temporal constraints (`PRIMARY KEY ... WITHOUT OVERLAPS`, `FOREIGN KEY ... PERIOD`) — useful when migrating SQL Server temporal tables, but no automated converter handles them yet, so model the constraints manually. [src9]

## Related Units

- [MySQL to PostgreSQL Migration](/software/migrations/mysql-to-postgresql/2026)
- [Oracle to PostgreSQL Migration](/software/migrations/oracle-to-postgresql/2026)
- [MongoDB to PostgreSQL Migration](/software/migrations/mongodb-to-postgresql/2026)
- [Diagnosing slow PostgreSQL queries](/software/debugging/postgresql-slow-queries/2026)
- [Database Migration Strategies](/software/patterns/database-migration-strategies/2026)