Familiar-looking SQL can conceal different type and concurrency rules. When migrating from PostgreSQL to SQL Server, translate the data contract and application behavior alongside the statements.
Inventory Types Before Migrating From PostgreSQL
Begin with the source schema, constraints, generated values, and representative data. Record ranges, maximum lengths, NULL behavior, and application expectations before mapping types. A type-name substitution alone cannot show whether an existing value will fit, compare correctly, or retain its time meaning after transfer.
I review actual values before approving the destination definition. Include uncommon Unicode characters, long text, historical dates, and duplicate-looking strings under the target collation. Those cases expose differences that a small sample of ordinary English names can miss. Keep rejected values visible rather than silently truncating or replacing them during loading.
Define the target SQL Server version as part of the contract. Some storage and query choices vary by version, including UTF-8 collations and native JSON storage. The examples use ordinary T-SQL constructs and identify any newer option separately. A migration plan needs one supported target, not a collection of unrelated feature assumptions.
Translate Generated IDs When Migrating From PostgreSQL
SERIAL uses sequence-backed behavior on the source, while SQL Server IDENTITY supplies generated values through its own column property. Neither is a promise of gap-free numbering. Preserve the existing key values during the load, then verify that the next generated value will not collide with imported rows.
CREATE TABLE dbo.Customer
(
CustomerID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
CustomerName nvarchar(100) NOT NULL,
IsActive bit NULL,
CreatedAt datetimeoffset(6) NOT NULL
);
SET IDENTITY_INSERT dbo.Customer ON;
INSERT dbo.Customer
(CustomerID,CustomerName,IsActive,CreatedAt)
VALUES (101,N'Cafe Sample',1,'2026-09-20T10:00:00+00:00');
SET IDENTITY_INSERT dbo.Customer OFF;This scratch example loads an explicit identity, not an entire source export. For a real load, reconcile imported keys, generator state, and any reserved future values. Source identity variants have their own rules for accepting explicit values. Do not assume every source generated column behaves identically to SERIAL or to the target property.
Preserve Text, Boolean, and Time Meaning
Map text and varchar to an appropriately bounded nvarchar or a deliberately selected varchar representation. Unicode nvarchar avoids reliance on a legacy code page. UTF-8 varchar is an option with supported SQL Server 2019 or later collations, but its byte-length limits still need testing against real source strings.
Map boolean to bit with true as one, false as zero, and NULL retained when the source permits unknown state. Rewrite predicates to match the target type instead of expecting a bare boolean expression to work unchanged. Decide whether previously unconstrained text needs a maximum length based on its real business contract.
For timestamp with time zone, preserve the instant through an explicit export convention, such as UTC, and load datetimeoffset with that known offset. The source does not retain the original named zone merely because the type's name mentions a zone. Store a separate zone identifier when future local-time interpretation requires it. Source timestamp without time zone instead needs its own documented local-time meaning.
Replace Paging With a Stable Order
LIMIT and OFFSET translate to ORDER BY with OFFSET and FETCH in T-SQL. The ordering must distinguish rows deterministically when the application relies on stable pages. An offset without an accepted order is a request for a page whose membership can change without a meaningful explanation.
SELECT CustomerID,CustomerName
FROM dbo.Customer
ORDER BY CustomerID
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;The numbers here are sample paging inputs. With only one scratch row in the table so far, this page comes back empty. For large offsets, consider an application contract using the last accepted key as the next-page boundary. Also test concurrent inserts and deletes, because deterministic ordering alone does not create one unchanged snapshot across separately requested pages.
Return Changed Rows With OUTPUT
RETURNING maps to the OUTPUT clause, with inserted and deleted row sources naming the appropriate values. Capture output into a table variable when the caller needs to process the returned identities. An explicit list keeps the application's returned shape stable across schema changes.
DECLARE @NewCustomers TABLE (CustomerID int,CustomerName nvarchar(100));
INSERT dbo.Customer (CustomerName,IsActive,CreatedAt)
OUTPUT inserted.CustomerID,inserted.CustomerName
INTO @NewCustomers (CustomerID,CustomerName)
VALUES (N'Adapter Customer',1,'2026-09-20T11:00:00+00:00');
SELECT CustomerID,CustomerName FROM @NewCustomers;Target OUTPUT values reflect the statement's row values before AFTER triggers run. That differs from assumptions a source application can make about returned trigger-modified data. Treat errors and rolled-back transactions as failed operations even if output rows were emitted. Verify the post-commit state when trigger behavior is part of the contract.
Recreate Case-Insensitive Search and Names
ILIKE needs a target case-insensitive comparison rule rather than a mechanical replacement of letters in the operator. Use an appropriate case-insensitive collation for LIKE when that matches the contract. Accent sensitivity and Unicode behavior are separate choices, and wildcard escaping also needs application testing.
SELECT CustomerID,CustomerName
FROM dbo.Customer
WHERE CustomerName COLLATE Latin1_General_100_CI_AS LIKE N'cafe%';
SELECT [CustomerID],[CustomerName]
FROM [dbo].[Customer];Double-quoted identifiers on the source differ from string literals. Square brackets provide explicit identifier delimiting in the target; double quotes can delimit identifiers with QUOTED_IDENTIFIER ON. Preserve required mixed-case or unusual names, but prefer a consistent naming contract where changes are authorized. Delimiters do not sanitize arbitrary text automatically.
Replace Conflict Handling With Concurrency Control
ON CONFLICT cannot be copied directly into T-SQL. MERGE is one possible redesign, but its matching and concurrency behavior require careful review. For a simple one-row upsert, UPDATE followed by INSERT in a protected transaction makes the decision path visible. Keep a unique key as the final data guarantee.
CREATE TABLE #AppSetting
(
SettingID int NOT NULL PRIMARY KEY,
SettingValue nvarchar(100) NOT NULL
);
DECLARE @SettingID int = 1, @SettingValue nvarchar(100) = N'Enabled';
IF @@TRANCOUNT <> 0 THROW 50000,'Use an independent session.',1;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE #AppSetting WITH (UPDLOCK,HOLDLOCK)
SET SettingValue = @SettingValue
WHERE SettingID = @SettingID;
IF @@ROWCOUNT = 0
INSERT #AppSetting (SettingID,SettingValue)
VALUES (@SettingID,@SettingValue);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;The temporary table demonstrates syntax, while concurrency rehearsal needs a shared persistent test table and multiple sessions. Define retry behavior for deadlocks and errors. I test conflicting inserts rather than assuming a successful single-session run validates an upsert. When migrating from PostgreSQL, preserving that behavior matters as much as preserving the ordinary happy-path values.
Redesign Arrays After Migrating From PostgreSQL
Source arrays can become a related child table with one row per element and an explicit position where order matters. That model supports foreign keys and element-level searches. JSON is another option when the attribute contract is document-oriented; use supported text storage with validation or the native JSON type on SQL Server 2025.
Which array properties must survive: duplicates, order, NULL elements, dimensions, or empty versus missing? Record them before choosing the target representation. A comma-separated string loses too much structure to serve as a casual default. Verify round trips using the actual source values and application readers.
Finish with reconciliation, generated-ID tests, paging tests, and concurrent write tests. Migrating from PostgreSQL succeeds when data and application semantics agree at the destination, rather than when the rewritten scripts merely parse without an error.
Related reading on this blog: PostgreSQL: Storing Unicode Characters is Easy and UTF-8 Collations in SQL Server 2019: When They Save Space.
A syntax translation is not a completed migration, it is one part of preserving the application's data contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
