Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 1 · Lesson 4 · 6 min read

T-SQL vs PostgreSQL SQL Syntax Differences

On this page
  1. A T-SQL query translated to PostgreSQL
  2. Limiting rows: TOP becomes LIMIT
  3. Joining strings: + becomes ||
  4. ISNULL becomes COALESCE
  5. Dates: GETDATE() becomes now()
  6. IDENTITY and SCOPE_IDENTITY()
  7. Identifier case: names fold to lowercase
  8. More function translations
  9. CROSS APPLY and OUTER APPLY become LATERAL
  10. 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:

SQL
-- 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:

SQL
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-SQLPostgreSQL
SELECT TOP 10 ... ORDER BY xSELECT ... ORDER BY x LIMIT 10
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLYThe same, or OFFSET 20 LIMIT 10
TOP 3 WITH TIESFETCH FIRST 3 ROWS WITH TIES (PostgreSQL 13+)
TOP 10 PERCENTNo direct equivalent

If your code already uses OFFSET ... FETCH, it works in PostgreSQL unchanged. For TOP PERCENT, compute the count. LIMIT accepts an expression:

SQL
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:

Code
SELECT 'Order ' || 42;            -- Order 42
SELECT 'a' || NULL;               -- NULL
SELECT concat('a', NULL, 'b');    -- ab

NULL 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:

SQL
SELECT coalesce(preferred_email, backup_email, 'none') AS contact_email FROM customers;

Dates: GETDATE() becomes now()

T-SQLPostgreSQL
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:

SQL
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-SQLPostgreSQL
SCOPE_IDENTITY() after an INSERTINSERT ... RETURNING order_id
OUTPUT inserted.*RETURNING *
SET IDENTITY_INSERT ... ONINSERT ... 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:

SQL
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:

SQL
CREATE TABLE "MixedCase" (id int);
SELECT * FROM MixedCase;     -- ERROR: relation "mixedcase" does not exist
SELECT * FROM "MixedCase";   -- works, and you need the quotes every time

Migration 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-SQLPostgreSQLWatch 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, RIGHTThe 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 ENDpg_input_is_valid needs PostgreSQL 16+
STRING_AGG(x, ', ')string_agg(x, ', ' ORDER BY x)Same name. Put the ORDER BY inside the parentheses.
SQL
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 | false

The 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:

SQL
-- 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;
SQL
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-SQLPostgreSQL
CROSS APPLY (...) AS rCROSS JOIN LATERAL (...) AS r
OUTER APPLY (...) AS rLEFT 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.