SQL Server → PostgreSQL: A Worked Example
A worked SQL Server to PostgreSQL example: TOP, IDENTITY, GETDATE/ISNULL/LEN, bracket identifiers, MERGE, and READ UNCOMMITTED habits.
SQL Server migrations split cleanly into two piles: the T-SQL syntax, which converts predictably, and the behavioral differences in functions and isolation, which don’t announce themselves. Swordfish carries 136 SQL Server rules. Reproduce these against the sample SQL Server app in the Docker image (cd ops && docker compose up -d, project source type sqlserver, point at /srv/test-apps, scan). The shared workflow lives in the Oracle post.
Syntax conversions
Row limiting: TOP (SS-001) becomes LIMIT:
-- SQL Server: SELECT TOP 10 * FROM orders ORDER BY created_at
-- Postgres: SELECT * FROM orders ORDER BY created_at LIMIT 10Identity columns (SS-S002) become identity columns; GETDATE() (SS-002) becomes CURRENT_TIMESTAMP; ISNULL() (SS-003) becomes COALESCE; square-bracket identifiers (SS-011) become double quotes:
-- SQL Server: SELECT [User].[Name], ISNULL([Phone],'') FROM [User]
-- Postgres: SELECT "User".name, COALESCE(phone,'') FROM "User"Date types — DATETIME/DATETIME2/SMALLDATETIME/DATETIMEOFFSET (SS-S008, SS-S005, SS-S009, SS-S010) map to timestamp / timestamptz; pick timestamptz whenever the application does timezone math.
The behavioral traps
LEN() ignores trailing spaces; length() doesn’t: SS-012 (high, behavioral). This is the SQL Server equivalent of Oracle’s empty-string trap. LEN('abc ') is 3 in SQL Server and 6 in PostgreSQL’s length(). Any validation, padding, or comparison logic built on string length silently changes answers. If you need the SQL Server behavior, the fix is length(rtrim(col)) — but only where the original meant “trimmed length,” which is a judgment call.
TOP without ORDER BY: MSS-BEH-002 (high). SELECT TOP 10 ... with no ORDER BY returns some ten rows, and the app may have come to depend on which ten. Converting to LIMIT 10 preserves the non-determinism; if the order actually mattered, you have to discover and add the intended ORDER BY.
WITH (NOLOCK): MSS-BEH-003 (medium/high). NOLOCK is everywhere in SQL Server reporting code and it means “dirty reads are fine.” PostgreSQL’s MVCC doesn’t have (or need) an equivalent hint; readers don’t block writers. The hint just gets removed, but flag it: code sprinkled with NOLOCK was often working around lock contention that PostgreSQL handles differently, so confirm there wasn’t a deliberate dirty-read dependency.
ISNULL with type-mismatched defaults: MSS-BEH-001 (medium). ISNULL(col, '') coerces the default to the column’s type and length, sometimes truncating. COALESCE follows standard type-resolution rules instead. Same intent, occasionally different result; review the ones inside string-length-sensitive logic.
@@IDENTITY vs SCOPE_IDENTITY(): SS-006 / MSS-BEH-005 (high). Both grab the last inserted identity value, but @@IDENTITY crosses scope (it’ll return an ID a trigger inserted, not the one you meant). In PostgreSQL the clean replacement is INSERT ... RETURNING id or lastval(). If the original code used @@IDENTITY and a trigger fires on that table, the migrated code has a chance to fix a latent bug — flag it so a human makes that call deliberately.
Stored procedures
T-SQL procedures (the SS-P* rules — PRINT, RAISERROR, THROW, etc.) need rewriting to PL/pgSQL. This is the labor-intensive part of a SQL Server migration and the place an LLM earns its keep — but the business logic inside those procs is exactly where inferred knowledge hides, so the rewrites are draft-and-review, never auto-merge.
The pattern for SQL Server: the syntax pile is quick, the stored-proc pile is slow, and the LEN/NOLOCK/@@IDENTITY behavioral findings are the ones that ship silently wrong. Next: DB2.
Reproduce this walkthrough against the bundled sample apps: github.com/EnterpriseDB/swordfish-sample-apps. Swordfish itself (Apache-2.0): github.com/EnterpriseDB/swordfish-migrations