STRING_AGG Function - SQL Server to PostgreSQL Migration

In SQL Server and PostgreSQL, the STRING_AGG function concatenates non-NULL string values from multiple rows into a single-row single-column value using the specified delimiter.

Note that syntax of the ORDER BY clause differs in SQL Server and PostgreSQL.

Consider a sample table:

  CREATE TABLE colors (name VARCHAR(30));
 
  -- Insert sample rows
  INSERT INTO colors VALUES ('Green');
  INSERT INTO colors VALUES ('Black');
  INSERT INTO colors VALUES (NULL);
  INSERT INTO colors VALUES ('Red');

SQL Server:

 -- Concatenate values using '; ' delimiter (NULL value excluded)
  SELECT STRING_AGG(name, '; ') FROM colors;
  /* Green; Black; Red */
 
  -- Order values before concatenation
  SELECT STRING_AGG(name, '; ') WITHIN GROUP (ORDER BY name) FROM colors;
  /* Black; Green; Red */

PostgreSQL:

  -- Concatenate values using '; ' delimiter (NULL value excluded)
  SELECT STRING_AGG(name, '; ') FROM colors;
  /* Green; Black; Red */
 
  -- Order values before concatenation (PostgreSQL uses a different syntax)
  SELECT STRING_AGG(name, '; ' ORDER BY name) FROM colors;
  /* Black; Green; Red */

For more information, see SQL Server to PostgreSQL Migration.