The T-SQL → PostgreSQL Phrasebook

Previously, in Part 3, we mapped SQL Server's data types onto their PostgreSQL counterparts. Now for the everyday vocabulary: the functions and clauses you type without thinking. This is the phrasebook — bookmark it, keep it open in a tab for your first few weeks, and you'll be surprised how quickly you stop needing it.

None of what follows is hard. You already know every concept here; you just know it under a different name. So each section is a side-by-side pair: the T-SQL you'd write on autopilot, and the PostgreSQL that does the same job.

Limiting and paging

-- SQL Server
SELECT TOP 10 * FROM orders ORDER BY placed_at DESC;

-- PostgreSQL
SELECT * FROM orders ORDER BY placed_at DESC LIMIT 10;

LIMIT goes at the end, which reads naturally once you're used to it. And here's a pleasant surprise: the standard paging syntax you've used since SQL Server 2012 works in PostgreSQL completely unchanged.

-- Works in BOTH engines, character for character
SELECT * FROM orders
ORDER BY placed_at DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

PostgreSQL also accepts the shorter LIMIT 10 OFFSET 20, which is what you'll see in most Postgres code in the wild.

NULL handling

-- SQL Server
SELECT ISNULL(nickname, 'friend') FROM users;

-- PostgreSQL (and SQL Server, and everything else)
SELECT COALESCE(nickname, 'friend') FROM users;

COALESCE is the ANSI-standard version of ISNULL, and it's better: it takes any number of arguments and returns the first non-NULL one. NULLIF is identical in both engines — no translation needed.

Dates and times

-- SQL Server
SELECT DATEADD(day, 7, starts_at),
       DATEDIFF(day, starts_at, ends_at)
FROM appointments;

-- PostgreSQL
SELECT starts_at + interval '7 days',
       ends_at - starts_at            -- an interval, e.g. '7 days 03:15:00'
FROM appointments;

-- Want just the number of whole days? Extract it:
SELECT EXTRACT(DAY FROM ends_at - starts_at) FROM appointments;

The interval arithmetic feels odd for about a day and then becomes something you miss everywhere else. As we covered in Part 3, if your columns are timestamptz, Postgres stores UTC under the hood — so your SYSUTCDATETIME() instinct is simply the default behavior, not something you have to remember to do.

Strings

-- SQL Server
SELECT 'Hello, ' + first_name + '!' FROM users;

-- PostgreSQL
SELECT 'Hello, ' || first_name || '!' FROM users;

One familiar trap carries over: || swallows NULL exactly the way + does in T-SQL — one NULL anywhere in the chain and the whole result is NULL. The NULL-safe answer is also the same in both engines: concat() treats NULLs as empty strings.

The id of the row you just inserted

You've spent years juggling SCOPE_IDENTITY(), dodging @@IDENTITY (which a trigger can silently hijack), and reaching for the OUTPUT clause when you needed to do it properly.

-- SQL Server
INSERT INTO appointments (patient_id, starts_at)
VALUES (42, '2026-09-01 10:00');
SELECT SCOPE_IDENTITY();

PostgreSQL replaces all three with one clause, and it's a genuine upgrade moment:

-- PostgreSQL
INSERT INTO appointments (patient_id, starts_at)
VALUES (42, '2026-09-01 10:00')
RETURNING appointment_id;

RETURNING can return any columns of the inserted row (or all of them with RETURNING *), it works for multi-row inserts, and — this is the part that makes converts smile — it works on UPDATE and DELETE too. Deleting old rows and getting back exactly what you deleted is one statement.

Upserts

-- SQL Server
MERGE INTO settings AS t
USING (VALUES ('theme', 'dark')) AS s (setting_key, setting_value)
    ON t.setting_key = s.setting_key
WHEN MATCHED THEN
    UPDATE SET setting_value = s.setting_value
WHEN NOT MATCHED THEN
    INSERT (setting_key, setting_value)
    VALUES (s.setting_key, s.setting_value);

-- PostgreSQL
INSERT INTO settings (setting_key, setting_value)
VALUES ('theme', 'dark')
ON CONFLICT (setting_key) DO UPDATE
    SET setting_value = EXCLUDED.setting_value;

EXCLUDED refers to the row you tried to insert. Modern PostgreSQL does have MERGE as well, so your existing statements can port over — but INSERT … ON CONFLICT is the idiom you'll see in nearly all Postgres code, and for the common "insert or update this one row" case it's shorter and harder to get wrong.

Old friends that need no translation

Plenty of your daily toolkit crosses over untouched:

That covers the queries you write all day. But a phrasebook only gets you so far — your stored procedures need a proper immigration plan, because PostgreSQL splits them into two different species. That's Part 5: stored procedures, functions and triggers in PL/pgSQL.