STUFF and SELECT FOR XML PATH - SQL Server to PostgreSQL Migration

In SQL Server, STUFF with SELECT FOR XML PATH statement is widely used to concatenate strings from multiple rows into a single row value.

Consider a sample table:

  CREATE TABLE colors (name VARCHAR(30));
 
  -- Insert sample rows
  INSERT INTO colors VALUES ('Green');  
  INSERT INTO colors VALUES ('White');
  INSERT INTO colors VALUES ('Blue');

In SQL Server you can use FOR XML PATH clause of SELECT statement to aggregate values from multiple rows:

SQL Server:

  -- Concatenate values using ',' delimiter; STUFF function is only used to remove the leading (,)
  SELECT STUFF((SELECT ',' + name FROM colors FOR XML PATH ('')), 1, 1, '');
  /* Green,White,Blue */
 
  -- Ordering by name now
  SELECT STUFF((SELECT ',' + name FROM colors ORDER BY name FOR XML PATH ('')), 1, 1, '');
  /* Blue,Green,White */

In PostgreSQL you can just use STRING_AGG function:

PostgreSQL:

  -- Concatenate values using ',' delimiter
  SELECT STRING_AGG(name, ',') FROM colors;
  /* Green,White,Blue */
 
    -- Ordering by name now
  SELECT STRING_AGG(name, ',' ORDER BY name) FROM colors;
  /* Blue,Green,White */

For more information, see SQL Server to PostgreSQL Migration.