Schema and data conversion is mostly mechanical. Stored procedures are not. They carry business rules, error handling and transaction behavior, and the two platforms disagree on all three. This guide covers the differences that cause rework.
The mechanical differences
ISNULL(a, b)becomesCOALESCE(a, b).SELECT TOP 10becomesLIMIT 10.GETDATE()becomesnow(), which is fixed for the transaction, orclock_timestamp(), which is not.DATEADDandDATEDIFFbecome interval arithmetic.- String concatenation with
+becomes||orconcat(). The two treat NULL differently. @@ROWCOUNTbecomesGET DIAGNOSTICS n = ROW_COUNT.SCOPE_IDENTITY()and theOUTPUTclause becomeRETURNING.#temptables becomeCREATE TEMP TABLE, which is visible for the session, not the procedure.sp_executesqlbecomesEXECUTE format(...) USING ....WITH (NOLOCK)is removed. Readers do not block writers under PostgreSQL's concurrency model.
One procedure, both sides
-- T-SQL
CREATE PROCEDURE dbo.CloseOrder @OrderId INT AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN;
UPDATE dbo.Orders SET Status = 'closed', ClosedAt = GETDATE()
WHERE OrderId = @OrderId AND Status = 'open';
IF @@ROWCOUNT = 0 THROW 50001, 'Order not open', 1;
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH
END
-- PL/pgSQL
CREATE PROCEDURE close_order(p_order_id integer)
LANGUAGE plpgsql AS $$
DECLARE
n integer;
BEGIN
UPDATE orders SET status = 'closed', closed_at = now()
WHERE order_id = p_order_id AND status = 'open';
GET DIAGNOSTICS n = ROW_COUNT;
IF n = 0 THEN
RAISE EXCEPTION 'Order not open' USING ERRCODE = 'P0001';
END IF;
END $$;
The PostgreSQL version is shorter because an unhandled error already rolls the transaction back. That same rule is the source of most conversion defects.
The differences that cause rework
- Errors abort the transaction. In SQL Server, many errors fail one statement and the batch continues. In PostgreSQL, the first error makes the transaction unusable until it rolls back. Code that relied on continuing after an error has to be redesigned, not translated.
- Exception blocks cost something. A
BEGIN ... EXCEPTIONblock opens a subtransaction. Inside a loop over many rows, that cost is visible. - Result sets. A T-SQL procedure can return several result sets. A PostgreSQL procedure cannot return them the same way. The usual targets are a function with
RETURNS TABLE, a set of functions, or refcursors. Each one changes the calling code. - Transaction control. PostgreSQL procedures can
COMMITinside the body only when called outside an explicit transaction. Functions cannot at all. Batch jobs that commit every N rows need a deliberate design. - Cross-database queries. Three-part names do not exist. The choices are schemas inside one database or
postgres_fdw. - Scheduled jobs. SQL Server Agent has no built-in counterpart. Jobs move to
pg_cronor an external scheduler, with their own alerting.
How to size the work
- Count procedures, functions and triggers. Then count the ones that are called. Unused code is common and should not be converted.
- Classify each one: mechanical, needs redesign, or replace with application code.
- Flag every procedure that returns multiple result sets, commits inside a loop, or continues after an error.
- Write the test before the conversion. Same inputs, same outputs, on both platforms.
The count of procedures in the redesign class is the best single predictor of the migration timeline. Get that number before agreeing a cutover date.
Where this leads
Datrick runs SQL Server to PostgreSQL migrations and operates the database afterward, with named engineers and a written scope. The service page describes the delivery model: SQL Server to PostgreSQL migration. For a first read of risk and effort, see the migration risk assessment.
Related: SQL Server to PostgreSQL migration · migration risk assessment · Production Migration Notes · PostgreSQL DBA services
