T-SQL vs PostgreSQL SQL Syntax Differences
On this page
- A T-SQL query translated to PostgreSQL
- Limiting rows: TOP becomes LIMIT
- Joining strings: + becomes ||
- ISNULL becomes COALESCE
- Dates: GETDATE() becomes now()
- IDENTITY and SCOPE_IDENTITY()
- Identifier case: names fold to lowercase
- More function translations
- CROSS APPLY and OUTER APPLY become LATERAL
- Check yourself
A T-SQL query translated to PostgreSQL
Here's a typical T-SQL query. Paste it into PostgreSQL and it fails on almost every line:
-- T-SQL
SELECT TOP 10 FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS FullName,
DATEDIFF(day, CreatedAt, GETDATE()) AS DaysAsCustomer
FROM dbo.Customers
ORDER BY CreatedAt DESC;The PostgreSQL version:
SELECT first_name || ' ' || coalesce(middle_name || ' ', '') || last_name AS full_name,
current_date - created_at::date AS days_as_customer
FROM customers
ORDER BY created_at DESC
LIMIT 10;Most of the differences you'll hit in daily work are in that one example. This lesson goes through them one at a time.
Limiting rows: TOP becomes LIMIT
T-SQL puts TOP after SELECT. PostgreSQL puts LIMIT at the end:
| T-SQL | PostgreSQL |
|---|---|
SELECT TOP 10 ... ORDER BY x | SELECT ... ORDER BY x LIMIT 10 |
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY | The same, or OFFSET 20 LIMIT 10 |
TOP 3 WITH TIES | FETCH FIRST 3 ROWS WITH TIES (PostgreSQL 13+) |
TOP 10 PERCENT | No direct equivalent |
If your code already uses OFFSET ... FETCH, it works in PostgreSQL unchanged. For TOP PERCENT, compute the count. LIMIT accepts an expression:
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT (SELECT ceil(count(*) * 0.10) FROM orders);Joining strings: + becomes ||
PostgreSQL keeps + for arithmetic. Strings are joined with ||, the SQL standard operator:
SELECT 'Order ' || 42; -- Order 42
SELECT 'a' || NULL; -- NULL
SELECT concat('a', NULL, 'b'); -- abNULL behaves as you're used to with SQL Server's default settings: anything joined with NULL becomes NULL. So keep wrapping nullable columns in COALESCE, or use concat(), which both databases have and which treats NULL as an empty string.
ISNULL becomes COALESCE
PostgreSQL has no ISNULL() function. Use COALESCE(), which SQL Server also supports, so you can start writing it today. It accepts any number of arguments and returns the first one that isn't NULL:
SELECT coalesce(preferred_email, backup_email, 'none') AS contact_email FROM customers;Dates: GETDATE() becomes now()
| T-SQL | PostgreSQL |
|---|---|
GETDATE(), SYSDATETIME() | now() or current_timestamp |
GETUTCDATE() | now() AT TIME ZONE 'UTC' |
CAST(GETDATE() AS date) | current_date |
DATEADD(day, 7, d) | d + INTERVAL '7 days' |
DATEDIFF(day, a, b) | b::date - a::date |
One behavior differs. now() returns the time the transaction started, and stays the same for every call inside that transaction. In SQL Server, GETDATE() gives a new value with each statement. If you need the real clock time inside a long transaction, for example to time each step of a batch, use clock_timestamp().
The ::date in the table is PostgreSQL's short cast syntax. x::date means the same as CAST(x AS date), and you'll see it everywhere.
IDENTITY and SCOPE_IDENTITY()
An auto-numbered key in PostgreSQL uses an identity column, which is close to T-SQL's IDENTITY:
CREATE TABLE orders_demo (
order_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_date timestamp NOT NULL DEFAULT now()
);You'll also see serial in older PostgreSQL code. It's a legacy shorthand, so prefer identity columns (Day 5 covers the types). Here's how the related T-SQL habits translate:
| T-SQL | PostgreSQL |
|---|---|
SCOPE_IDENTITY() after an INSERT | INSERT ... RETURNING order_id |
OUTPUT inserted.* | RETURNING * |
SET IDENTITY_INSERT ... ON | INSERT ... OVERRIDING SYSTEM VALUE |
DBCC CHECKIDENT (..., RESEED, n) | ALTER TABLE ... ALTER COLUMN order_id RESTART WITH n |
RETURNING is PostgreSQL's version of the OUTPUT clause. It works on INSERT, UPDATE and DELETE, and gives you the new id without a second query:
INSERT INTO orders_demo DEFAULT VALUES
RETURNING order_id, order_date;Identifier case: names fold to lowercase
This one causes quiet bugs. SQL Server, with a typical collation, ignores the case of names, so CustomerID and customerid are the same column.
PostgreSQL folds unquoted names to lowercase. CREATE TABLE Customers (CustomerID int) creates a table called customers with a column customerid. Unquoted references are folded the same way, so SELECT CustomerID FROM Customers still works.
The trouble starts with double quotes, which keep the exact case and make the name case-sensitive forever:
CREATE TABLE "MixedCase" (id int);
SELECT * FROM MixedCase; -- ERROR: relation "mixedcase" does not exist
SELECT * FROM "MixedCase"; -- works, and you need the quotes every timeMigration tools often create quoted, mixed-case names to preserve the SQL Server names exactly. Avoid that. Convert names to lowercase snake_case during the migration (CustomerID becomes customer_id), and you'll never need quotes again.
More function translations
These come up in almost every migrated query. All of them were run on PostgreSQL 18:
| T-SQL | PostgreSQL | Watch out for |
|---|---|---|
LEN(s) | length(s) | LEN ignores trailing spaces, length counts them: LEN('abc ') is 3, length('abc ') is 5 |
CHARINDEX('world', s) | strpos(s, 'world') | The arguments swap places |
SUBSTRING(s, 1, 4), LEFT, RIGHT | The same names | |
DATEPART(year, d), YEAR(d) | extract(year FROM d) | |
DATEFROMPARTS(2026, 10, 1) | make_date(2026, 10, 1) | |
CONVERT(varchar, d, 120), FORMAT(d, ...) | to_char(d, 'YYYY-MM-DD HH24:MI') | Style numbers become pattern strings |
IIF(c, a, b) | CASE WHEN c THEN a ELSE b END | |
TRY_CAST(s AS int) | CASE WHEN pg_input_is_valid(s, 'integer') THEN s::int END | pg_input_is_valid needs PostgreSQL 16+ |
STRING_AGG(x, ', ') | string_agg(x, ', ' ORDER BY x) | Same name. Put the ORDER BY inside the parentheses. |
SELECT length('Hello') AS len,
strpos('hello world', 'world') AS pos,
extract(year FROM DATE '2026-10-23') AS yr,
to_char(TIMESTAMP '2026-10-23 14:05', 'YYYY-MM-DD HH24:MI') AS formatted,
pg_input_is_valid('42x', 'integer') AS castable;
-- 5 | 7 | 2026 | 2026-10-23 14:05 | falseThe LEN difference matters for code that checks LEN(code) = 0 on padded char columns. Decide whether trailing spaces count, and use length(rtrim(s)) where the old behavior is what you want.
CROSS APPLY and OUTER APPLY become LATERAL
T-SQL uses APPLY to run a subquery once per row, for example each customer's two most recent orders. PostgreSQL calls this a LATERAL join:
-- T-SQL
SELECT c.CustomerName, r.OrderId, r.OrderTotal
FROM dbo.Customers c
CROSS APPLY (SELECT TOP 2 o.OrderId, o.OrderTotal
FROM dbo.Orders o WHERE o.CustomerId = c.CustomerId
ORDER BY o.OrderDate DESC) AS r;SELECT c.customer_name, r.order_id, r.order_total
FROM customers c
CROSS JOIN LATERAL (
SELECT o.order_id, o.order_total
FROM orders o
WHERE o.customer_id = c.customer_id
ORDER BY o.order_date DESC
LIMIT 2
) AS r;| T-SQL | PostgreSQL |
|---|---|
CROSS APPLY (...) AS r | CROSS JOIN LATERAL (...) AS r |
OUTER APPLY (...) AS r | LEFT JOIN LATERAL (...) AS r ON true |
CROSS APPLY STRING_SPLIT(s, ',') | CROSS JOIN LATERAL unnest(string_to_array(s, ',')) AS t(value) |
CROSS APPLY OPENJSON(doc) | CROSS JOIN LATERAL jsonb_array_elements(doc) AS t(value) |
Tested on a small dataset: customers with no orders disappear with CROSS JOIN LATERAL and stay, with NULLs, with the LEFT JOIN version, exactly as CROSS and OUTER APPLY behave. The ON true looks odd, but the join condition is already inside the subquery.
Check yourself
SELECT * FROM scores ORDER BY points DESC FETCH FIRST 5 ROWS WITH TIES;, which PostgreSQL supports since version 13. Like T-SQL's WITH TIES, it returns extra rows that tie with the fifth, and it requires an ORDER BY, because ties only mean something within an ordering.
The migration tool created the table with a quoted name, "Orders". Unquoted names fold to lowercase, so PostgreSQL looks for orders. Rename the table to lowercase, for example ALTER TABLE "Orders" RENAME TO orders.
now() and CURRENT_TIMESTAMP return the time the transaction started, and stay the same for the whole transaction. That's useful for consistent timestamps, but every step in one transaction gets the same value. Use clock_timestamp(), which returns the actual current time on every call, to measure step durations.
LEFT JOIN LATERAL (SELECT ... WHERE o.customer_id = c.customer_id ORDER BY ... LIMIT 1) AS r ON true. The LEFT join keeps customers whose subquery returns no row.
Use || or concat(). 'Hello ' || NULL returns NULL, just like + with CONCAT_NULL_YIELDS_NULL on in SQL Server, while concat('Hello ', NULL) treats NULL as an empty string. Pick deliberately, since the two behave differently when values are missing.