Last updated: 2026-06-14

T-SQL vs PL/pgSQL: a reference for moving SQL Server code to PostgreSQL

T-SQL (Transact-SQL) is SQL Server's procedural dialect; PL/pgSQL is PostgreSQL's. The query layer — SELECT, JOIN, window functions, CTEs — is close enough that most statements move with light edits. The procedural layer — variables, control flow, error handling, the built-in functions you reach for without thinking — is where the two diverge, and it's where a SQL Server developer's muscle memory works against them. This page is a side-by-side reference for that gap.

It exists because of a specific, honest boundary in how Jam SQL Studio migrates databases. When you migrate SQL Server tables to PostgreSQL, Jam translates table shape and data mechanically — types, identity, defaults, CHECK/UNIQUE, foreign keys, indexes, and rows all come across automatically. What it will not do is auto-translate the bodies of stored procedures, functions, triggers, and views, because T-SQL and PL/pgSQL don't map 1:1 and a wrong translation is worse than an honest "port this by hand." So Jam gives you the cross-engine object inventory — which routines exist, which are missing — and this page gives you the dialect map to port them.

The big picture: three structural differences

Before the function-by-function tables, three differences explain most of the friction:

1. Procedures are functions. PostgreSQL has both FUNCTION and (since v11) PROCEDURE, but the everyday unit is the function, and a T-SQL stored procedure that returns a result set is most naturally re-authored as a PL/pgSQL function that RETURNS TABLE or RETURNS SETOF. A T-SQL procedure can just SELECT and the rows flow to the client; a PL/pgSQL function must declare its return shape.
2. The body is a quoted string. A PL/pgSQL function body lives inside a dollar-quoted literal ($$ ... $$) with an explicit LANGUAGE plpgsql. There's no batch separator like GO; statements end with ; and the whole body is one object.
3. Variables are declared, not sprinkled. T-SQL lets you DECLARE @x INT anywhere in a batch. PL/pgSQL requires all variables in a single DECLARE section before the BEGIN, and they have no @ sigil — a bare name that can't collide with a column.

Here is the same trivial "get a customer's order count" routine in each dialect, so the shape is concrete:

-- T-SQL (SQL Server)
CREATE PROCEDURE dbo.GetOrderCount @CustomerId INT
AS
BEGIN
    DECLARE @Total INT;
    SELECT @Total = COUNT(*) FROM dbo.Orders WHERE CustomerId = @CustomerId;
    SELECT @Total AS OrderCount;
END;

-- PL/pgSQL (PostgreSQL)
CREATE FUNCTION get_order_count(p_customer_id INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    v_total INT;
BEGIN
    SELECT COUNT(*) INTO v_total FROM orders WHERE customer_id = p_customer_id;
    RETURN v_total;
END;
$$;

Note the four changes you'll make on almost every routine: the @param becomes a named parameter, the DECLARE moves into its own section, SELECT @x = ... becomes SELECT ... INTO x, and the trailing "result set" SELECT becomes a RETURN.

Variables and control flow

The cursor row almost always simplifies: a T-SQL cursor loop becomes a PL/pgSQL FOR r IN SELECT ... LOOP, which is both shorter and faster because it doesn't materialise a server cursor.

Error handling

T-SQL's TRY/CATCH maps to a PL/pgSQL BEGIN ... EXCEPTION block, but the surrounding semantics differ enough to call out:

-- PL/pgSQL: catch a duplicate-key violation by name, not by number
BEGIN
    INSERT INTO accounts(email) VALUES (p_email);
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'Account % already exists', p_email;
END;

The biggest conceptual shift: in PL/pgSQL the exception block automatically rolls back to an implicit savepoint taken at its BEGIN. You don't inspect XACT_STATE() or manage SET XACT_ABORT — entering the EXCEPTION handler means the statements in that block were already undone, and only that block.

Built-in functions that change name

This is the table people actually bookmark. These are the SQL Server built-ins whose name or signature changes in PostgreSQL — the silent breakers, because the T-SQL version simply doesn't exist rather than erroring helpfully.

The two that bite hardest are the ones that don't error: + for string concatenation (in PostgreSQL 'a' + 'b' is a type error, and 1 + 'x' can silently misbehave), and SCOPE_IDENTITY() (PostgreSQL has no session-scoped last-insert; you use RETURNING on the INSERT itself). For a complete column-type map — the schema side of the same move — see the SQL Server ↔ PostgreSQL data type mapping reference.

Temp tables and table variables

PostgreSQL temp tables are visible for the whole session by default; add ON COMMIT DROP when you want the T-SQL table-variable feel (gone at the end of the statement's transaction). One real gotcha: creating and dropping many temp tables in a hot loop bloats the system catalogs in PostgreSQL more than in SQL Server, so prefer a single temp table you TRUNCATE, or a CTE / array, over per-iteration temp tables.

Triggers: the largest rewrite

Triggers are the hardest objects to port, because the model is fundamentally different. T-SQL triggers are statement-level and expose the affected rows through the INSERTED and DELETED pseudo-tables. PostgreSQL triggers are usually row-level (FOR EACH ROW) and expose a single NEW and OLD record — and the trigger logic must live in a separate FUNCTION that the CREATE TRIGGER statement points at. There is no way to mechanically turn a set-based INSERTED/DELETED trigger into a row-based NEW/OLD one without understanding the intent. This is covered in depth in Convert SQL Server triggers to PostgreSQL.

How Jam SQL Studio fits in

Jam SQL Studio doesn't pretend to translate any of this for you — that's the point. What it does instead:

• Moves the mechanical 90%. Tables, types, identity, defaults, constraints, foreign keys, indexes, and all the data migrate across engines automatically. See Migrate SQL Server to PostgreSQL.
• Inventories the manual 10%. A cross-engine Schema Compare lists every view, procedure, function, and trigger that exists on the SQL Server side but not yet on PostgreSQL — your port checklist, by name.
• Keeps the source in front of you. Script the original T-SQL object (Script as CREATE), open a PostgreSQL query editor beside it, and rewrite using the tables above — both connections live in the same window.

The mechanical migration and this dialect reference are two halves of the same move: Jam carries the schema and data, you carry the logic, and nothing gets silently mistranslated in between.

Frequently asked questions

Related guides

• Cross-engine database migration — the overview of the migration this dialect reference complements.
• Migrate SQL Server to PostgreSQL — the table + data migration this reference complements.
• Convert SQL Server stored procedures to PostgreSQL — a worked procedure port.
• Convert SQL Server triggers to PostgreSQL — the INSERTED/DELETED → NEW/OLD rewrite.
• T-SQL → PostgreSQL function cheat sheet — the function table above, printable.
• SQL Server ↔ PostgreSQL data type mapping — the schema-side companion.