Database migrations look "done" until Monday morning.
The stored procedures are converted. The schema is on PostgreSQL. Then the order API fails — because nobody inventoried nativeQuery = true in Spring or FromSqlRaw in EF Core.
Here are five patterns that survive the DBA's .sql bundle but break in production:
1. SELECT TOP
PostgreSQL uses LIMIT. Easy to miss inside a 40-character @Query string.
2. GETDATE() / SYSDATETIME()
Becomes NOW() or CURRENT_TIMESTAMP — but only if you find every occurrence in application code.
3. ISNULL()
PostgreSQL prefers COALESCE. Sounds trivial until it's buried in a Dapper query.
4. TRY/CATCH in batches
No direct equivalent in PL/pgSQL the same way. Often lives in dynamic SQL built in C#.
5. MERGE / OUTPUT clauses
Common in SQL Server scripts and in hand-written repository methods — rarely in the same migration spreadsheet.
The inventory problem
DBA-owned: .sql files, procedures, schema scripts.
App-owned: .java, .cs, .js, .py, .php with embedded T-SQL.
Two inventories. One cutover date.
What we do before cutover
Scan application repos for embedded SQL → file + line + risk report → human review on flagged lines.
I built SQLShifter for this after one too many post-migration fire drills:
- ~95% accuracy on common SS↔PG patterns (methodology)
- Free: 2 full analyses/month, 3 files
- Files not stored on server
Not a replacement for migration planning — a pre-flight check.
What's the worst post-migration surprise you've seen outside the official script bundle?
Top comments (0)