T-SQL to PostgreSQL: The 10 Conversion Gotchas

Most T-SQL converts to PostgreSQL mechanically. SELECT, JOIN, GROUP BY, window functions, and the common string and date functions rename or rearrange but keep the same shape. The list below is different: these are the constructs where PostgreSQL has no direct equivalent, or where the equivalent changes what the query means. They are the same known-hard constructs our free assessment tool classifies in your own .rdl files, and the same table our conversion pipeline uses to decide what it can rewrite automatically. For the inventory work that comes before any of this, see how to inventory an SSRS estate.

What Doesn't Need a Rewrite

Before the gotchas, the contrast worth having in mind: these three are close to a straight substitution, syntax swaps that need no manual review in the common case.

-- GETDATE() / SYSDATETIME()  ->  NOW()
SELECT GETDATE();                          -- T-SQL
SELECT NOW();                              -- PostgreSQL

-- TOP n  ->  LIMIT n
SELECT TOP 10 * FROM orders ORDER BY order_date DESC;   -- T-SQL
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10; -- PostgreSQL

-- ISNULL  ->  COALESCE
SELECT ISNULL(phone, 'N/A') FROM customers;    -- T-SQL
SELECT COALESCE(phone, 'N/A') FROM customers;  -- PostgreSQL

Two of them carry a smaller, honest asterisk. ISNULL returns the declared type and length of its first argument and truncates to fit; COALESCE returns the type with the highest precedence among all its arguments instead, so a truncation you relied on in T-SQL can silently stop happening. And GETDATE() reads the clock at the moment the statement runs, while PostgreSQL's NOW() returns the timestamp at the start of the current transaction, so two calls to NOW() inside the same transaction return the same value where two calls to GETDATE() in the same batch can differ. Neither difference breaks the substitution outright, but both are worth knowing before you assume identical behavior everywhere.

The ten below are not like that. Each one either has no direct PostgreSQL equivalent, or the closest equivalent changes the query's behavior in a way that needs a human decision, not just a find-and-replace.

1. PIVOT and UNPIVOT

T-SQL's PIVOT turns row values into columns in one clause. PostgreSQL has no PIVOT keyword at all. The standard replacement is conditional aggregation, a SUM or COUNT per output column guarded by a FILTER clause.

SELECT category, [2024], [2025]
FROM sales_by_year
PIVOT (SUM(amount) FOR sale_year IN ([2024], [2025])) AS p;

SELECT category,
  SUM(amount) FILTER (WHERE sale_year = 2024) AS "2024",
  SUM(amount) FILTER (WHERE sale_year = 2025) AS "2025"
FROM sales_by_year
GROUP BY category;

The conversion approach is mechanical once you know the pattern, but it is not automatic: every distinct pivoted value needs its own FILTER expression, so a PIVOT with a dynamic column list (built at runtime) needs a human to enumerate the values first. T-SQL's PIVOT also groups implicitly by every column you did not pivot or aggregate, category here, so the PostgreSQL rewrite needs an explicit GROUP BY naming those same columns instead of inheriting it for free. UNPIVOT, the mirror operation that turns columns back into rows, has no PostgreSQL keyword either and is rebuilt with a UNION ALL of one SELECT per source column; the free scan flags it as its own construct, separate from PIVOT.

2. Recursive CTEs

SQL Server infers that a CTE is recursive from its structure. PostgreSQL requires the RECURSIVE keyword to be stated explicitly, and it is strict about type consistency across the anchor and recursive branches of the UNION.

WITH OrgChart AS (
  SELECT EmployeeId, ManagerId, 0 AS Depth
  FROM Employees WHERE ManagerId IS NULL
  UNION ALL
  SELECT e.EmployeeId, e.ManagerId, oc.Depth + 1
  FROM Employees e
  JOIN OrgChart oc ON e.ManagerId = oc.EmployeeId
)
SELECT * FROM OrgChart;

WITH RECURSIVE org_chart AS (
  SELECT employee_id, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;

Adding RECURSIVE is trivial. The real work is verifying that every column in the anchor query and the recursive query resolves to the same PostgreSQL type. SQL Server is more permissive about implicit widening across the UNION than PostgreSQL is.

3. Temp Tables and Table Variables

The #temp naming convention and @table variables do not exist in PostgreSQL. PostgreSQL has real temporary tables, session-scoped by default, but the syntax and the naming pattern both change.

CREATE TABLE #Staging (Id INT, Total DECIMAL(10,2));
INSERT INTO #Staging SELECT Id, SUM(Amount) FROM Orders GROUP BY Id;
SELECT * FROM #Staging;

CREATE TEMP TABLE staging (id INT, total NUMERIC(10,2));
INSERT INTO staging SELECT id, SUM(amount) FROM orders GROUP BY id;
SELECT * FROM staging;

For most SSRS report queries a CTE replaces the temp table entirely and avoids the conversion altogether. Reserve CREATE TEMP TABLE for the cases where the intermediate result is genuinely reused across several later statements in the same batch. One more difference worth planning for: a PostgreSQL temp table lives for the entire session unless you drop it explicitly or create it ON COMMIT DROP, and a connection-pooled application can hold the same underlying session open across many unrelated requests. A temp table created and never cleaned up can leak into a later request on the same pooled connection, or collide when that request tries to create a table with the same name.

4. Cursors

T-SQL allows a cursor as a standalone batch, opened, fetched, and closed with WHILE @@FETCH_STATUS driving the loop. PostgreSQL supports SQL-level cursors too, DECLARE ... CURSOR FOR a query, then FETCH, MOVE, and CLOSE, but only inside a transaction block, and there is no SQL-level loop construct to drive FETCH the way WHILE @@FETCH_STATUS does. That loop has to move into PL/pgSQL, which means a cursor built around this pattern is never a drop-in replacement, it is a restructure.

DECLARE @Id INT;
DECLARE cur CURSOR FOR SELECT Id FROM Orders;
OPEN cur;
FETCH NEXT FROM cur INTO @Id;
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC dbo.ProcessOrder @Id;
  FETCH NEXT FROM cur INTO @Id;
END
CLOSE cur;
DEALLOCATE cur;

DO $$
DECLARE
  cur CURSOR FOR SELECT id FROM orders;
  rec RECORD;
BEGIN
  FOR rec IN cur LOOP
    PERFORM process_order(rec.id);
  END LOOP;
END $$;

Most cursors in SSRS report queries turn out to be a row-by-row simulation of something a set-based query already does. Check for that first. The PL/pgSQL rewrite above is the fallback for the genuine cases, not the default answer.

5. MERGE

T-SQL's MERGE combines insert, update, and delete against a target table in one statement. For the common insert-or-update case, INSERT ... ON CONFLICT is still the more portable conversion target, it has worked on every supported PostgreSQL version for years, where PostgreSQL's own MERGE is newer.

MERGE INTO Inventory AS tgt
USING Staging AS src ON tgt.Sku = src.Sku
WHEN MATCHED THEN UPDATE SET tgt.Qty = src.Qty
WHEN NOT MATCHED THEN INSERT (Sku, Qty) VALUES (src.Sku, src.Qty);

INSERT INTO inventory (sku, qty)
SELECT sku, qty FROM staging
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;

ON CONFLICT handles insert-or-update cleanly, but ON CONFLICT (sku) requires sku to carry a unique index or constraint. If staging contains duplicate skus, PostgreSQL raises "ON CONFLICT DO UPDATE command cannot affect row a second time" rather than silently picking one, so deduplicate staging first. ON CONFLICT also has no delete arm on its own: a MERGE that also removes unmatched target rows needs a second statement behind it. PostgreSQL 17 and later closes that gap directly: its own MERGE statement supports WHEN NOT MATCHED BY SOURCE THEN DELETE, the same three-way MATCHED / NOT MATCHED / NOT MATCHED BY SOURCE pattern T-SQL uses, in one statement. On PostgreSQL 15 or 16, MERGE exists but without that BY SOURCE clause, so ON CONFLICT plus a separate DELETE remains the practical choice.

6. CROSS APPLY and OUTER APPLY

APPLY runs a correlated subquery once per row on the left side, the pattern SSRS reports lean on for "most recent record per parent" logic. PostgreSQL's equivalent is a LATERAL join.

SELECT c.CustomerId, top1.OrderId
FROM Customers c
CROSS APPLY (
  SELECT TOP 1 OrderId FROM Orders o
  WHERE o.CustomerId = c.CustomerId
  ORDER BY OrderDate DESC
) AS top1;

SELECT c.customer_id, top1.order_id
FROM customers c
CROSS JOIN LATERAL (
  SELECT order_id FROM orders o
  WHERE o.customer_id = c.customer_id
  ORDER BY order_date DESC
  LIMIT 1
) AS top1;

CROSS APPLY becomes CROSS JOIN LATERAL, which drops left-side rows with no match. OUTER APPLY becomes LEFT JOIN LATERAL ... ON true, which keeps them. Picking the wrong one changes row counts, and in a paginated report that changes totals.

7. FOR XML and FOR JSON

T-SQL serializes a result set to XML or JSON directly in the SELECT clause. PostgreSQL builds the same output with its own aggregate functions over a subquery.

SELECT CustomerId, Name
FROM Customers
FOR JSON PATH;

SELECT json_agg(row_to_json(c))
FROM (SELECT customer_id, name FROM customers) c;

FOR XML maps similarly to xmlagg and xmlelement. The output is not byte-identical between the two engines, so any downstream consumer that parses the XML or JSON structurally, rather than just displaying it, needs to be checked against the new shape.

8. TOP ... WITH TIES

Plain TOP converts to LIMIT without incident. TOP WITH TIES does not, because it returns every row tied with the last qualifying row, and LIMIT has no concept of a tie. PostgreSQL 13 and later has a direct, one-to-one equivalent for this exact pattern.

SELECT TOP 3 WITH TIES Name, Score
FROM Leaderboard
ORDER BY Score DESC;

SELECT name, score
FROM leaderboard
ORDER BY score DESC
FETCH FIRST 3 ROWS WITH TIES;

On an older PostgreSQL version, or when you want the tie-breaking logic visible in the query itself, a window function does the same job:

SELECT name, score FROM (
  SELECT name, score,
    RANK() OVER (ORDER BY score DESC) AS rnk
  FROM leaderboard
) ranked
WHERE rnk <= 3;

This is the one auto-convertible-looking gotcha on the list: a naive converter sees TOP and reaches for LIMIT, and the query still runs, it just silently drops tied rows that the original report included.

9. IDENTITY, @@IDENTITY, and SCOPE_IDENTITY

T-SQL generates surrogate keys with an IDENTITY column and reads the value back with SCOPE_IDENTITY. PostgreSQL's equivalent column syntax is different, and getting the new key back is a single-statement operation rather than a follow-up query.

CREATE TABLE Orders (
  Id INT IDENTITY(1,1) PRIMARY KEY,
  CustomerId INT
);
INSERT INTO Orders (CustomerId) VALUES (42);
SELECT SCOPE_IDENTITY();

CREATE TABLE orders (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customer_id INT
);
INSERT INTO orders (customer_id) VALUES (42) RETURNING id;

GENERATED BY DEFAULT is the safer migration target over GENERATED ALWAYS: ALWAYS rejects an explicit INSERT that supplies its own id value outright, which breaks the historical-data reload every migration needs at least once to preserve existing keys. RETURNING is still strictly better than a follow-up call, one round trip instead of two, and no ambiguity about which scope's insert the identity value came from, the cross-scope bleed between @@IDENTITY and a trigger-fired insert on another table that SCOPE_IDENTITY exists specifically to avoid. The gotcha is remembering to add RETURNING at all: a query translated line by line will run the IDENTITY column correctly and then still try to SELECT a T-SQL-style identity function that does not exist.

10. Linked Servers and OPENQUERY

Four-part names and OPENQUERY let a T-SQL query reach across servers in one statement. PostgreSQL's answer is a foreign data wrapper, a configured connection to the remote source that gets queried like a local table.

SELECT * FROM OPENQUERY(
  RemoteSrv,
  'SELECT CustomerId, Name FROM dbo.Customers'
);

CREATE SERVER remote_srv FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote-host', dbname 'crm');
CREATE FOREIGN TABLE customers_remote (customer_id INT, name TEXT)
  SERVER remote_srv OPTIONS (schema_name 'public', table_name 'customers');

SELECT * FROM customers_remote;

postgres_fdw only reaches another PostgreSQL database. A linked server pointed at SQL Server, Oracle, or a flat file needs a different foreign data wrapper, or in some cases an application- level join instead of a database-level one. This is the gotcha most likely to change the architecture of the report, not just its SQL.

Two More the Assessment Also Flags

Dynamic SQL, a query assembled and executed from a string via EXEC or sp_executesql, cannot be read statically at all: the real query only exists at runtime, so it converts to PostgreSQL's EXECUTE format(...) pattern inside a function, but each instance needs a human to read what the string actually builds. CLR assemblies, custom .NET code compiled into SQL Server, have no PostgreSQL equivalent at all; the logic has to move into PL/pgSQL, a PL/Python function, or the application layer. Both are counted as known-hard, and both are among the constructs that alone flag a report for manual attention rather than automated conversion.

Where This List Comes From

This is not a generic SQL dialect comparison. It is the same known-hard construct table our conversion pipeline checks every query against. When a query contains none of these ten, plus dynamic SQL and CLR, our pipeline converts it automatically and validates the result with an EXPLAIN plan check plus a LIMIT 1 execution against a live PostgreSQL database, with up to three automated fix rounds if the first attempt does not pass. The same assessment also counts subreports, custom VB.NET code blocks, and stored-procedure-backed datasets as hard signals of their own, because that work is not visible in the query text either, so a report can still need a human look even when every query in it is clean. Ours is 95.8% effective across 190+ production reports, our historical rate on our own library, not a prediction for yours. When a query does contain one of these constructs, that is exactly the signal our free assessment surfaces before you commit to anything.