T-SQL to PostgreSQL: The Conversion Patterns That Trip Everyone Up

If you're migrating SSRS reports away from SQL Server, the rendering engine is the easy part. The hard part is the SQL. T-SQL and PostgreSQL are both SQL, but the dialect differences are significant enough to break nearly every non-trivial query during migration.

After converting nearly 200 production report queries from T-SQL to PostgreSQL, I've cataloged every pattern that causes problems. This isn't a theoretical comparison — these are the actual conversion patterns that failed validation, produced wrong results, or threw runtime errors in real migrations.

1. ISNULL vs COALESCE

This is the most common conversion and the one people get wrong most often — not because the syntax is hard, but because of a subtle behavioral difference.

SELECT ISNULL(middle_name, '') AS middle_name
FROM employees

SELECT COALESCE(middle_name, '') AS middle_name
FROM employees

The straightforward replacement works for most cases, but there's a catch: ISNULL returns the data type of the first argument, while COALESCE returns the data type with highest precedence among all arguments. This matters when you're mixing types:

-- T-SQL: ISNULL(varchar(10), varchar(50)) returns varchar(10)
-- Truncation can occur silently

-- PostgreSQL: COALESCE picks the widest compatible type
-- No truncation, but different result length

In practice, this type-precedence difference causes issues in about 2% of conversions — usually in reports that concatenate ISNULL results into fixed-width output fields. If your reports produce print-ready documents with strict column widths, test these carefully.

2. TOP vs LIMIT

The syntax change is straightforward, but the position in the query is completely different — and that trips up both humans and automated converters.

SELECT TOP 10 employee_name, hire_date
FROM employees
ORDER BY hire_date DESC

SELECT employee_name, hire_date
FROM employees
ORDER BY hire_date DESC
LIMIT 10

The gotcha is TOP inside subqueries. T-SQL allows SELECT TOP 1 in correlated subqueries as a common pattern for "get the most recent value" logic:

-- T-SQL pattern
SELECT e.name,
  (SELECT TOP 1 r.review_date
   FROM reviews r WHERE r.emp_id = e.id
   ORDER BY r.review_date DESC) AS last_review
FROM employees e

-- PostgreSQL equivalent
SELECT e.name,
  (SELECT r.review_date
   FROM reviews r WHERE r.emp_id = e.id
   ORDER BY r.review_date DESC
   LIMIT 1) AS last_review
FROM employees e

Automated converters sometimes strip the TOP 1 but forget to add LIMIT 1 at the end of the subquery. The query still runs — but now it returns a random row instead of the most recent one. The report looks fine at first glance because the data is plausible. This is the kind of silent bug that only surfaces during UAT when someone compares specific values.

3. Date Functions: The Biggest Minefield

Date handling is where T-SQL and PostgreSQL diverge the most. Nearly every date function has a different name, different syntax, or different behavior.

-- GETDATE() → NOW() or CURRENT_TIMESTAMP
-- DATEADD(day, 30, start_date) → start_date + INTERVAL '30 days'
-- DATEDIFF(day, start, end) → (end::date - start::date)
-- CONVERT(VARCHAR, date, 101) → TO_CHAR(date, 'MM/DD/YYYY')
-- CAST(date AS DATE) → date::date
-- DATEPART(year, date) → EXTRACT(YEAR FROM date)
-- EOMONTH(date) → (date_trunc('month', date) + INTERVAL '1 month - 1 day')::date

The DATEDIFF conversion deserves special attention. T-SQL's DATEDIFF counts the number of boundaries crossed, not elapsed time. This means:

-- T-SQL: DATEDIFF(year, '2025-12-31', '2026-01-01') = 1
-- (crosses one year boundary)

-- Naive PostgreSQL: EXTRACT(YEAR FROM '2026-01-01'::date)
--                 - EXTRACT(YEAR FROM '2025-12-31'::date) = 1
-- This happens to match for years, but...

-- T-SQL: DATEDIFF(month, '2026-01-31', '2026-02-01') = 1
-- PostgreSQL age-based: The dates are 1 day apart
-- You need boundary-crossing logic, not elapsed time

For month and year DATEDIFF, the boundary-crossing semantics usually match simple subtraction. For day, hour, and minute intervals, the differences compound. Every DATEDIFF call in your RDL queries needs manual verification against the original output.

4. String Concatenation and the + Operator

T-SQL uses + for string concatenation. PostgreSQL uses ||. Simple enough — until NULL enters the picture.

-- T-SQL (with default CONCAT_NULL_YIELDS_NULL ON):
SELECT first_name + ' ' + middle_name + ' ' + last_name
-- If middle_name is NULL, the entire expression is NULL

-- PostgreSQL:
SELECT first_name || ' ' || middle_name || ' ' || last_name
-- Same behavior — NULL propagates

-- Safe alternative in both:
SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name)
-- CONCAT ignores NULLs in both T-SQL and PostgreSQL

The conversion from + to || is mechanical, but you need to audit for NULL handling. Many T-SQL reports wrap concatenation operands in ISNULL to prevent NULL propagation. After converting ISNULL to COALESCE, verify that the fallback values still make sense. An empty string fallback in T-SQL might need to be adjusted if the column type is numeric in the concatenation chain.

5. Square Bracket Identifiers

T-SQL uses square brackets to quote identifiers: [Column Name], [Order], [Group]. PostgreSQL uses double quotes: "Column Name", "Order", "Group".

-- T-SQL
SELECT [First Name], [Order], [Group]
FROM [Employee Data]

-- PostgreSQL
SELECT "First Name", "Order", "Group"
FROM "Employee Data"

This is a pure find-and-replace, but the volume is high — a typical SSRS report library has thousands of bracketed identifiers. The real problem is case sensitivity. PostgreSQL treats double-quoted identifiers as case-sensitive, while T-SQL square brackets are case-insensitive. If your SQL Server tables were created as EmployeeData but your queries reference [employeedata], the bracket-to-quote conversion will fail on PostgreSQL because "employeedata" and "EmployeeData" are different identifiers. The safest approach: lowercase all identifiers in your PostgreSQL schema and strip the quotes entirely where possible.

6. Temp Tables and Table Variables

T-SQL reports frequently use #temp tables and @table variables for intermediate results. PostgreSQL handles temporary tables differently.

-- T-SQL
SELECT emp_id, SUM(hours) AS total_hours
INTO #monthly_hours
FROM timesheets
WHERE month = @ReportMonth
GROUP BY emp_id

SELECT e.name, t.total_hours
FROM employees e
JOIN #monthly_hours t ON t.emp_id = e.id

-- PostgreSQL (using CTE instead)
WITH monthly_hours AS (
  SELECT emp_id, SUM(hours) AS total_hours
  FROM timesheets
  WHERE month = $1
  GROUP BY emp_id
)
SELECT e.name, t.total_hours
FROM employees e
JOIN monthly_hours t ON t.emp_id = e.id

For most SSRS report queries, CTEs (Common Table Expressions) are a cleaner replacement for temp tables. Both T-SQL and PostgreSQL support CTEs, so in many cases you can refactor the T-SQL to use CTEs before the migration, test it on SQL Server, and then convert the CTE-based version to PostgreSQL — which usually requires only minor syntax changes. For complex multi-step stored procedures that use temp tables extensively, PostgreSQL's CREATE TEMP TABLE works, but the syntax and scoping rules differ enough that each procedure needs individual attention.

7. Stored Procedures to PostgreSQL Functions

If your SSRS reports call stored procedures as their data source, this is the most labor-intensive conversion. T-SQL stored procedures and PostgreSQL functions have fundamentally different execution models.

The pragmatic approach: if a stored procedure is just a wrapper around a SELECT query with parameters, inline the query directly into the RDL report definition. This eliminates the procedure-to-function conversion entirely. Reserve the full function conversion for procedures that genuinely need procedural logic (loops, cursors, multi-step transformations).

8. CROSS APPLY and OUTER APPLY

T-SQL's APPLY operators are widely used in SSRS reports for row-by-row correlated operations. PostgreSQL supports LATERAL JOIN as the equivalent.

-- T-SQL
SELECT e.name, latest.review_date, latest.score
FROM employees e
CROSS APPLY (
  SELECT TOP 1 review_date, score
  FROM reviews r
  WHERE r.emp_id = e.id
  ORDER BY review_date DESC
) latest

-- PostgreSQL
SELECT e.name, latest.review_date, latest.score
FROM employees e
CROSS JOIN LATERAL (
  SELECT review_date, score
  FROM reviews r
  WHERE r.emp_id = e.id
  ORDER BY review_date DESC
  LIMIT 1
) latest

OUTER APPLY maps to LEFT JOIN LATERAL ... ON true. The semantic difference is that OUTER APPLY preserves the left-side row even when the subquery returns no results (like a LEFT JOIN), while CROSS APPLY filters them out (like an INNER JOIN). Getting this wrong changes your row count — and in a paginated report, that means pages shift and totals change.

A Practical Conversion Strategy

After converting hundreds of queries, the most reliable approach is a three-pass strategy:

ReportBridge automates passes 1 and 2 — AI conversion plus EXPLAIN validation with up to five automated correction rounds. Pass 3 (output comparison) still benefits from human review, especially for reports with complex business logic where "correct" output requires domain knowledge to verify.