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) becomes COALESCE(a, b).
  • SELECT TOP 10 becomes LIMIT 10.
  • GETDATE() becomes now(), which is fixed for the transaction, or clock_timestamp(), which is not.
  • DATEADD and DATEDIFF become interval arithmetic.
  • String concatenation with + becomes || or concat(). The two treat NULL differently.
  • @@ROWCOUNT becomes GET DIAGNOSTICS n = ROW_COUNT.
  • SCOPE_IDENTITY() and the OUTPUT clause become RETURNING.
  • #temp tables become CREATE TEMP TABLE, which is visible for the session, not the procedure.
  • sp_executesql becomes EXECUTE 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 ... EXCEPTION block 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 COMMIT inside 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_cron or 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