LISTAGG Function - Oracle to PostgreSQL to Migration

In Oracle you can use LISTAGG function to concatenate strings from multiple rows into a single row value. In PostgreSQL you can use STRING_AGG function.

Consider a sample table:

  CREATE TABLE cities (name VARCHAR(30));
 
  -- Insert sample rows
  INSERT INTO cities VALUES ('Seville');
  INSERT INTO cities VALUES ('Warsaw');
  INSERT INTO cities VALUES ('Boston');

Oracle:

  -- Concatenate values using ';' delimiter
  SELECT LISTAGG(name, ';') WITHIN GROUP (ORDER BY name)
  FROM cities;
  # Boston;Seville;Warsaw

PostgreSQL:

  -- Concatenate values using ';' delimiter
  SELECT STRING_AGG(name, ';' ORDER BY name)
  FROM cities;
  # Boston;Seville;Warsaw

For more information, see Oracle to PostgreSQL Migration.