T-SQL to PostgreSQL Function Cheat Sheet

Published: 2026-06-14

A scannable map from T-SQL built-in functions to their PostgreSQL equivalents, with the gotchas called out — the ones that don't error, they just return something subtly different. Every mapping here was checked against PostgreSQL 16.

This is the function-level companion to the T-SQL vs PL/pgSQL reference and the data type mapping. It's the table you keep open while porting a stored procedure, function, or trigger after migrating your tables to PostgreSQL.

Null handling and conditionals

T-SQL	PostgreSQL	Note
`ISNULL(x, y)`	`COALESCE(x, y)`	COALESCE takes N args; ISNULL forces type of first arg
`NULLIF(x, y)`	`NULLIF(x, y)`	Same in both
`IIF(c, a, b)`	`CASE WHEN c THEN a ELSE b END`	No IIF in PostgreSQL
`CHOOSE(2, a, b, c)`	`CASE n WHEN 1 THEN a ... END` or array index	No CHOOSE

Strings

T-SQL	PostgreSQL	Note
`LEN(s)`	`length(s)`	Gotcha: LEN ignores trailing spaces, length doesn't. Use `length(rtrim(s))` to match exactly
`a + b`	`a \|\| b`	Gotcha: `+` is numeric-only in PostgreSQL; string `+` is a type error
`SUBSTRING(s, 2, 3)`	`substring(s FROM 2 FOR 3)` / `substr(s, 2, 3)`	1-based in both
`CHARINDEX(n, h)`	`position(n IN h)` / `strpos(h, n)`	Argument order flips; both 1-based, 0 if absent
`REPLACE(s, a, b)`	`replace(s, a, b)`	Same
`UPPER / LOWER`	`upper / lower`	Same
`LTRIM / RTRIM / TRIM`	`ltrim / rtrim / trim`	Same; PostgreSQL trim takes optional chars
`LEFT(s, 3)` / `RIGHT(s, 3)`	`left(s, 3)` / `right(s, 3)`	Same
`STUFF(...)`	`overlay(s PLACING r FROM p FOR n)`	Different name/signature
`STRING_AGG(s, ',')`	`string_agg(s, ',')`	Same; ordering via `ORDER BY` inside
`STRING_SPLIT(s, ',')`	`unnest(string_to_array(s, ','))`	Different idiom; also `regexp_split_to_table`
`FORMAT(x, 'C')`	`to_char(x, '...')`	Pattern language differs; no locale "C" format

Dates and times

T-SQL	PostgreSQL	Note
`GETDATE()` / `SYSDATETIME()`	`now()` / `current_timestamp`	now() returns timestamptz; cast if you need plain timestamp
`GETUTCDATE()`	`now() AT TIME ZONE 'UTC'`
`DATEADD(day, 7, d)`	`d + INTERVAL '7 days'`	Interval arithmetic, not a function
`DATEDIFF(day, a, b)`	`(b::date - a::date)`	Gotcha: date subtraction yields integer days; for other units use EXTRACT on the interval
`DATEPART(year, d)` / `YEAR(d)`	`EXTRACT(YEAR FROM d)`	Also `date_part('year', d)`
`CONVERT(date, d)`	`d::date`	Cast
`EOMONTH(d)`	`(date_trunc('month', d) + INTERVAL '1 month - 1 day')::date`	No single EOMONTH
`DATEFROMPARTS(y, m, d)`	`make_date(y, m, d)`

Numbers

T-SQL	PostgreSQL	Note
`CEILING(x)`	`ceil(x)`	Also `ceiling(x)` works in PostgreSQL
`FLOOR / ROUND / ABS`	`floor / round / abs`	Same
`x % y` (modulo)	`x % y` or `mod(x, y)`	Same operator
`POWER(x, y)`	`power(x, y)`	Same
`RAND()`	`random()`	Both return [0,1)
`ISNUMERIC(s)`	`s ~ '^[0-9]+(\.[0-9]+)?$'`	No direct equivalent; regex or a safe-cast helper

Identity, sequences, and system

T-SQL	PostgreSQL	Note
`SCOPE_IDENTITY()` / `@@IDENTITY`	`INSERT ... RETURNING id`	Gotcha: no session "last identity"; capture on the INSERT
`NEWID()`	`gen_random_uuid()`	Core since PostgreSQL 13
`NEXT VALUE FOR seq`	`nextval('seq')`
`@@ROWCOUNT`	`GET DIAGNOSTICS n = ROW_COUNT;`	In PL/pgSQL; also `FOUND` boolean
`@@VERSION`	`version()`
`DB_NAME()` / `SUSER_SNAME()`	`current_database()` / `current_user`
`CAST(x AS INT)` / `CONVERT(INT, x)`	`CAST(x AS INT)` / `x::int`	CAST is standard in both; CONVERT is T-SQL only

The five that bite hardest

If you skim nothing else, internalise these — they don't raise an error, they just behave differently:

+ for strings. 'a' + 'b' is a type error in PostgreSQL; use ||. And 5 + NULL is NULL in both, but a T-SQL string-plus-number concatenation has no PostgreSQL analogue.
LEN vs length. LEN('abc ') is 3 in SQL Server, length('abc ') is 5 in PostgreSQL. Wrap in rtrim() to match.
SCOPE_IDENTITY(). There is no session-scoped last id. Use RETURNING on the INSERT.
CHARINDEX argument order. CHARINDEX(needle, haystack) but position(needle IN haystack) / strpos(haystack, needle) — easy to flip and get 0.
GETDATE() doesn't exist. The single most common "function does not exist" error in a freshly ported procedure. It's now().

Port the bodies, let Jam carry the tables

This cheat sheet is for the part you write by hand. The part you shouldn't — moving the tables, types, constraints, and rows — is what Jam SQL Studio's cross-engine migration does in a few clicks, and its cross-engine Schema Compare tells you exactly which functions, procedures, and triggers still need porting. Script the T-SQL original in one tab, write the PL/pgSQL in another, and keep this table between them.

For worked examples, see converting stored procedures and converting triggers.

Frequently asked questions

What is the PostgreSQL equivalent of ISNULL?

COALESCE(x, y). PostgreSQL has no ISNULL function; COALESCE is the standard-SQL equivalent and also accepts more than two arguments, returning the first non-null. Note SQL Server's ISNULL forces the result to the type of the first argument, whereas COALESCE uses normal type resolution, which can matter for fixed-length strings.

What replaces GETDATE() in PostgreSQL?

now() or current_timestamp for the transaction start time, and clock_timestamp() for the actual wall-clock time at the moment of the call. SQL Server's GETDATE() returns datetime; PostgreSQL's now() returns timestamptz, so cast if you need a plain timestamp.

How do I find a substring position like CHARINDEX in PostgreSQL?

Use position(needle IN haystack) or strpos(haystack, needle). Both return a 1-based index and 0 when not found, matching CHARINDEX. The argument order differs: CHARINDEX(needle, haystack) puts the needle first, position() reads needle IN haystack, and strpos() puts the haystack first.

Download Jam SQL Studio — free for personal use on macOS, Windows, and Linux.