SQL Server PostgreSQL Migration: What to Audit in Your Application

Migrating an application from SQL Server to PostgreSQL is not a configuration change, it is a systems migration. The two engines share SQL as a common language in the same way that Spanish and Portuguese share Latin roots: close enough to look familiar, different enough to fail in ways that are hard to predict and harder to debug in production.

The gap shows up at every layer of the stack. Connection strings are formatted differently. Identifiers that SQL Server treats as case-insensitive become case-sensitive in PostgreSQL. T-SQL functions like ISNULL, DATEADD, and CONVERT do not exist. Stored procedures need to be rewritten from scratch. Raw SQL strings that have worked for years will start throwing syntax errors etc.

None of these failures are loud. Most of them surface as a 500 on a specific endpoint, a silent empty result set, or a data integrity issue that only shows up days after cutover when someone notices numbers are wrong. By that point, the connection between cause and effect is invisible.

The checklist below was written to make these failure modes visible before they happen. It covers every category of incompatibility we encountered during a real production migration like connection handling, SQL dialect differences, ORM configuration, stored procedures, and schema introspection. Each section describes the concept, why the two engines differ, and exactly what to search for in your codebase.

If you skip a section because it does not seem relevant, it probably means the issue is already hidden in your codebase, not that it is absent. The migrations that go smoothly are the ones where every item was audited and consciously signed off, not assumed.

1. Database Configuration Concept: SQL Server and PostgreSQL cannot share a single connection alias. During a dual-database transition, you need separate named aliases for each engine, and all code that references an alias by string must be updated.

What to audit:

All settings/config files for hardcoded database alias strings.

Whether you need a dual-alias strategy (e.g. mydb_mssql + mydb_postgres) or a clean cutover.

Any constant in the codebase whose name implies a specific engine rename to engine-neutral equivalents.

Any allow-lists or registries of DB aliases that need updating.

PostgreSQL-specific connection options: search_path if your objects live in a named schema (not public), sslmode, port (ODBC connections often omit port; PostgreSQL URIs need it explicitly).

1. Connection String Parsing Concept: SQL Server connection strings are typically ODBC keyword format (Server=;Database=;Uid=;Pwd=). PostgreSQL uses URIs (postgresql://user:pass@host:5432/db). Any code that parses or introspects a raw connection string will break on the wrong format.

What to audit:

Any code that parses a raw DSN string to extract host, port, user, password, or database name.

Whether the parser validates that the string format matches the expected engine (fail loudly, not silently).

Whether port extraction is handled (ODBC often implies a port; PostgreSQL URIs carry it explicitly).

1. Hardcoded DB Routing / using() Calls Concept: Every Model.objects.using('some_alias') or equivalent call that names a specific database alias will break if that alias is renamed or split. You also need a strategy for queries where the target database depends on runtime state (e.g. which customer is being served).

What to audit:

Grep for every using( call, each one is a potential breakage point.

Any base class or mixin that sets a default using alias, these propagate to all subclasses silently.

Any discovery queries ("which database owns this record?") - these need to fan out across all aliases and merge results, with a clear tie-breaking rule.

1. Data Access Layer (DAL) - SQL Dialect Abstraction Concept: The single biggest source of incompatibilities is raw SQL strings built inline throughout the codebase. Every place that constructs a SQL fragment is a potential T-SQL site. The right fix is a DAL abstraction that encapsulates every dialect difference as a method, not scattered if sql_server: ... else: ... branches.

What to audit:

Every raw SQL string or fragment built in application code. Common indicators: f"SELECT ...", "SELECT " + variable, .format(...) on a SQL string, .raw(...), cursor.execute(...).

Whether a DAL class exists with per-dialect implementations. If not, create one as a foundation before touching individual call sites.

That the DAL resolves the correct implementation from a database connection or alias at runtime and that resolution handles subclassed backends (connection poolers, custom wrappers) via MRO walking, not exact type comparison.

The following sections (5–18) are the individual SQL dialect differences to implement in that DAL.

A note on ORM usage

If your application uses the ORM exclusively and correctly, many of the dialect differences in points 5–16 are handled automatically. The ORM knows the difference between TOP and LIMIT. It generates the right date arithmetic. It quotes identifiers correctly for the target engine.

This checklist is not written for the happy path. It is written for everything the ORM does not cover and in any codebase of meaningful size, that list is longer than expected.

Raw SQL survives in performance-critical queries, complex aggregations, reporting layers, database migrations, and anywhere someone reached for cursor.execute() to solve a problem quickly. Stored procedures, triggers, query validators, and schema introspection queries are inherently outside what any ORM models. And model metadata.i.e. db_table, db_column, ordering is always your responsibility regardless of how well the ORM is used.

Before deciding a section does not apply to your codebase, grep for it. The sections that seem least relevant are often the ones hiding the most surprises.

1. Object and Identifier Casing

Concept: SQL Server is case-insensitive by default collation. PostgreSQL folds unquoted identifiers to lowercase and is case-sensitive. Contacts, contacts, and CONTACTS are three different tables on PostgreSQL.

What to audit:

All db_table and db_column values on ORM models, they must match the actual lowercase names in PostgreSQL.

Inline SQL that references table or column names as string literals, lowercase them or quote them properly.

Column-name lookups against query results: SQL Server returns the declared PascalCase header; PostgreSQL returns lowercase. A .get(col) or .get(col.lower()) fallback is a common safeguard during transition.

Any serializer source= that mirrors a column name.

INFORMATION_SCHEMA queries that filter on TABLE_NAME or COLUMN_NAME, wrap comparisons in lower() on both sides.

User-saved or database-stored SQL, these may contain old bracket-quoted [PascalCase] identifiers that need re-quoting at execution time.

1. Identifier Quoting Syntax

Concept: SQL Server uses brackets ([column_name]) to quote identifiers. PostgreSQL uses double-quotes ("column_name"). Brackets are valid array subscript syntax in PostgreSQL, so any bracketed identifier causes a syntax error.

What to audit:

Grep for [ in raw SQL strings, every bracket-quoted identifier needs to be rewritten.

Any code that appends [ + name + ], replace with a quote_identifier(name) DAL call.

Schema-qualified objects like [dbo].[ProcedureName], replace with a qualify_object(name) DAL call that returns [dbo].[Name] for SQL Server and schema.name for PostgreSQL.

Column aliases: AS 'Label' (single-quoted) is accepted by SQL Server; PostgreSQL treats it as a string literal. Replace with AS "Label" (double-quoted), routed through quote_identifier.

Any code that calls .upper() on a full SQL string for normalisation, this corrupts double-quoted identifiers. Replace with case-insensitive keyword splitting.

1. Row-Limiting Syntax (TOP vs LIMIT) Concept: SQL Server uses SELECT TOP n before the column list. PostgreSQL uses LIMIT n appended after the ORDER BY.

What to audit:

Grep for TOP (with a space, inside SQL strings).

Any code that builds a limit clause as a string variable (e.g. limit_clause = f"TOP {n}"), refactor to carry an integer and let the DAL build the full statement.

DAL method signature: limited_select(select_list, remainder, count).

1. NULL Coalescing - ISNULL vs COALESCE Concept: ISNULL(expr, default) is T-SQL. SQL standard (and PostgreSQL) uses COALESCE(expr, default).

What to audit:

Grep for ISNULL( in raw SQL strings.

Replace via a coalesce(expr, default) DAL method.

1. Date Arithmetic - DATEADD vs Interval Expressions Concept: DATEADD(day, 7, column) is T-SQL only. PostgreSQL uses column + interval '7 day'.

What to audit:

Grep for DATEADD( in raw SQL strings.

Replace via a date_add(unit, value, expr) DAL method.

1. Date/Time Formatting - CONVERT vs TO_CHAR / to_timestamp Concept: SQL Server uses CONVERT(VARCHAR(10), date_col, style_number) for formatting and CONVERT(DATETIME, string, style) for parsing. PostgreSQL uses TO_CHAR(expr, 'pattern') and to_timestamp(expr, 'pattern').

What to audit:

Grep for CONVERT( in raw SQL strings.

Map the SQL Server style codes (101, 103, 102, 108, etc.) to their TO_CHAR / to_timestamp pattern equivalents.

Replace via format_datetime(expr, fmt) and cast_to_datetime(expr) DAL methods.

1. Safe Numeric Parsing - TRY_PARSE Concept: TRY_PARSE(expr AS numeric) returns NULL for non-numeric input. PostgreSQL has no equivalent; a straight cast (expr::numeric) raises an exception on non-numeric input.

What to audit:

Grep for TRY_PARSE( in raw SQL strings.

PostgreSQL replacement: CASE WHEN expr ~ '^[+-]?[0-9]*.?[0-9]+$' THEN expr::numeric END, a regex guard before the cast.

Replace via a parse_numeric(expr) DAL method.

1. String Aggregation - STUFF/FOR XML PATH vs string_agg Concept: SQL Server fakes string aggregation with STUFF((SELECT sep+col FROM ... FOR XML PATH('')), 1, 1, ''). This is T-SQL only, and it also XML-escapes content (& becomes &). PostgreSQL has native string_agg(col, sep).

What to audit:

Grep for FOR XML PATH in raw SQL strings.

Note the XML-escaping side effect, if callers relied on it, behaviour will change.

Replace via a string_agg_subquery(expr, remainder, sep) DAL method.

1. String Concatenation Operator Concept: SQL Server uses + for string concatenation in SQL. PostgreSQL uses ||.

What to audit:

Raw SQL that concatenates string columns or literals using +.

Replace via a string_concat(*exprs) DAL method, or just use || directly if you are fully on PostgreSQL.

1. Character-from-Code Function - CHAR vs CHR Concept: CHAR(n) returns the character for ASCII/Unicode code point n on SQL Server. PostgreSQL uses CHR(n).

What to audit:

Grep for CHAR( in raw SQL strings - distinguish from VARCHAR / NVARCHAR type names.

Replace via a char_code(n) DAL method.

1. LIKE Type Coercion Concept: SQL Server coerces types in LIKE comparisons implicitly. PostgreSQL requires the left operand to be text - LIKE against a non-text column (e.g. int, uuid) raises a type error.

What to audit:

Any col LIKE %s where col might not be declared as a text type in the database.

PostgreSQL fix: col::text LIKE %s.

Replace via a like_operand(expr) DAL method (no-op on SQL Server, ::text cast on PostgreSQL).

1. Query Validation - SET NOEXEC vs EXPLAIN Concept: SET NOEXEC ON; ; SET NOEXEC OFF validates SQL syntax without executing it - T-SQL only. PostgreSQL uses EXPLAIN for the same purpose.

What to audit:

Any "validate this query without running it" feature in the application.

Edge case: an empty statement needs special-casing before dialect dispatch - EXPLAIN with no body is a PostgreSQL syntax error.

Replace via a check_query_sql(sql) DAL method.

1. INSERT … Returning the Generated PK Concept: SQL Server uses OUTPUT inserted.pk (or DECLARE @t table; INSERT ... OUTPUT inserted.pk INTO @t; SELECT * FROM @t). PostgreSQL uses trailing RETURNING pk.

What to audit:

Any custom INSERT that captures the generated primary key - this includes custom ORM compiler subclasses.

Bulk insert helpers that return a list of new PKs.

Replace via insert_returning_pk_sql(sql, pk_column) and batch_insert_returning_pk(...) DAL methods.

1. Dynamic DDL (Triggers, Procedures) Concept: T-SQL trigger bodies use INSERTED/DELETED pseudo-tables, cursors, @@Fetch_Status, DEALLOCATE. PostgreSQL uses PL/pgSQL row-level trigger functions. The entire syntax is different.

What to audit:

Any application code that dynamically generates or executes CREATE TRIGGER DDL.

PostgreSQL triggers require two steps: create the trigger function first (CREATE OR REPLACE FUNCTION ... RETURNS trigger), then the trigger. Dropping requires dropping both.

Trigger existence checks: sys.objects WHERE type = 'TR' → pg_trigger WHERE tgname = %s.

1. Stored Procedure Invocation Concept: This is the most complex area. T-SQL procedure invocation (exec [dbo].[Name] @param = value) is incompatible with PostgreSQL on multiple dimensions simultaneously.

Actions:

Inventory every stored procedure call in the codebase.

Convert all named-parameter dicts to positional lists - there is no PostgreSQL equivalent.

Create a StoredProceduresBase with SqlServerStoredProcedures and PostgresStoredProcedures subclasses. Each subclass has one method per procedure, handling the dialect-specific invocation and any column-name remapping.

For row-returning procedures on PostgreSQL, wrap calls in transaction.atomic, open a named portal (uuid4().hex), call the procedure passing the portal name, then FETCH ALL FROM portal.

Add a column map for each result-returning procedure to translate PostgreSQL lowercase names to the application's expected keys.

Bridge error modes to a common exception type so callers are insulated from dialect differences.

1. INFORMATION_SCHEMA Queries Concept: PostgreSQL INFORMATION_SCHEMA differs from SQL Server in several ways that are easy to miss.

What to audit:

Schema filter: PostgreSQL requires TABLE_SCHEMA = 'your_schema' to avoid matching same-named tables in multiple schemas. SQL Server is usually single-schema and works without it.

SELECT * column headers: PostgreSQL returns lowercase header names; SQL Server returns the column names as declared. If you rely on row['COLUMN_NAME'], it may need to become row['column_name'] or have explicit aliases.

User-defined types: PostgreSQL data_type returns USER-DEFINED for custom types (e.g. citext). The actual type is in udt_name - check both.

Byte vs character lengths: SQL Server character_maximum_length for nvarchar is in bytes (2 per character). PostgreSQL reports characters directly. Any code that halves the length for nvarchar must not do so for varchar on PostgreSQL.

SQL Server catalogue views (sys.objects, sys.all_columns, sys.types, sys.triggers) - these have no PostgreSQL equivalent. Use pg_class, pg_attribute, pg_trigger, etc. via DAL methods instead.

Any column-type classification sets in the application (e.g. lists of "text types", "date types") - extend with PostgreSQL spelling variants.

1. ORM Model Metadata What to audit:

All managed = False models: db_table and db_column values must be lowercase (matching the PostgreSQL object names).

Meta.ordering that uses column name strings - lowercase them.

Serializer source= fields that mirror column names - lowercase them.

Any get_or_create, update_or_create, or filter calls using keyword arguments that resolve to db_column names - these should be fine if the model fields are correct, but worth spot-checking.

Case Study

During a recent migration of a multi-tenant marketing platform from SQL Server to PostgreSQL, two issues stood out.

First, a single database alias had to split into two at runtime, every hardcoded using() call across views, serializers broke silently, and what looked like a find-and-replace turned into a week of tracing call sites.

Second, a set of ORM models started returning empty results on PostgreSQL with no errors, SQL Server's case-insensitive collation had been quietly accommodating PascalCase table and column names for years; PostgreSQL's case-sensitive collation simply found nothing and moved on. No exception, no log entry, just missing data.

Conclusion

A SQL Server to PostgreSQL migration is one of those projects that looks straightforward on a planning document and reveals its true complexity only when you start reading the codebase carefully. The engines are similar enough that most things work, and different enough that the things that don't work fail quietly, in production, weeks after cutover.

This checklist exists because experience is an expensive way to learn which things break. Every section above maps to a real failure mode encountered in a real migration. None of them are theoretical.

If there is one overarching lesson from going through this process, it is that the migration itself is the easy part. The hard part is the audit.i.e. finding every assumption baked into the codebase that was never a problem because SQL Server quietly accommodated it.

The five most important things to take away:

1. Build the DAL before you touch anything else. Every dialect difference in this checklist needs to live in one place. If you start fixing ISNULL calls inline, you will fix them inconsistently, miss half of them, and have no way to verify you got them all. A DAL with per-dialect implementations means each fix happens once, is testable in isolation, and is provably complete.

2. Casing breaks more things than any single SQL function. ISNULL throwing an error is obvious and immediate. A db_table = 'Contacts' silently querying nothing, or worse, querying the wrong table, can go undetected through your entire test suite and surface as a data issue in production. Lowercase everything, verify it against the actual schema, and treat any PascalCase in a model definition as a red flag.

3. The ORM protects you only as far as you used it. Every cursor.execute(), every .raw(), every SQL string built with string formatting is outside the ORM's protection. Do not assume the ORM solved this. Grep for raw SQL before you draw any conclusions about how much of the checklist applies to you.

4. Test isolation is not the same as integration testing. Unit tests with mocked databases will pass on both engines. Only tests that run against a real PostgreSQL instance.i.e. with real data, real schema, real constraints will catch the failures this checklist describes. If your test suite does not run against PostgreSQL before cutover, you do not have a test suite for this migration.

5. The dual-database transition period is riskier than the cutover. Running both engines simultaneously feels safer. In practice it means two connection aliases, two code paths, two sets of assumptions, and double the surface area for bugs. Keep the transition window as short as operationally possible, have a clear tie-breaking rule for cross-engine queries.