SQL Server → PostgreSQL

STRING_AGG and FOR XML PATH: String Aggregation in SQL Server → PostgreSQL Migration

Replace T-SQL STUFF + FOR XML PATH with STRING_AGG or ARRAY_AGG in PostgreSQL for concatenated lists.

The classic SQL Server pattern for row concatenation is STUFF with a correlated subquery and FOR XML PATH. In PostgreSQL, STRING_AGG with ORDER BY inside GROUP BY solves the same case with less code.

STRING_AGG equivalent

PostgreSQL

SELECT d.department,
  STRING_AGG(e.name, ', ' ORDER BY e.name) AS names
FROM departments d
JOIN employees e ON e.dept_id = d.id
GROUP BY d.id, d.department;

Analisador de Impacto

String aggregation is one of the slowest patterns to convert by hand. Test the snippet below in the converter.

Abrir Análise de Projeto →