What's the equivalent for LISTAGG (Oracle database) in PostgreSQL?

sql postgresql string-aggregation listagg
cy221 picture cy221 · Apr 10, 2015 · Viewed 42.2k times · Source

I have to replace the Oracle driver with the newest PostgreSQL. PostgreSQL doesn't know the function LISTAGG. I have to concat values by comma separated. What's the equivalent for the Oracle's function LISTAGG in PostgreSQL?

Answer

Vivek S. picture Vivek S. · Apr 10, 2015

The equivalent function in PostgreSQL is STRING_AGG()

SELECT STRING_AGG (column_name,', ') 
FROM my_table

string_agg : input values concatenated into a string, separated by delimiter

For example, get list of all agreement_id then represent it in a string, in Apache Ofbiz 17.12.04

SELECT STRING_AGG(agreement_id, ', ') FROM agreement_item;

-- result
-- "8000, DS-1000-SALES, DS-1000-PURCH, 9000, AGR_SALES"

Related questions

How to concatenate strings of a string field in a PostgreSQL 'group by' query?

I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table: ID COMPANY_ID EMPLOYEE 1 1 Anna 2 1 Bill 3 2 Carol 4 2 Dave and I wanted to group by company_…

Read More
Postgresql GROUP_CONCAT equivalent?

I have a table and I'd like to pull one row per id with field values concatenated. In my table, for example, I have this: TM67 | 4 | 32556 TM67 | 9 | 98200 TM67 | 72 | 22300 TM99 | 2 | 23009 TM99 | 3 | 11200 And I'd like to output: TM67 | 4,9,72 | 32556,98200,22300 TM99 | 2,3 | 23009,11200 In MySQL I …

Read More
How to sort the result from string_agg()

I have a table: CREATE TABLE tblproducts ( productid integer, product character varying(20) ) With the rows: INSERT INTO tblproducts(productid, product) VALUES (1, 'CANDID POWDER 50 GM'); INSERT INTO tblproducts(productid, product) VALUES (2, 'SINAREST P SYP 100 ML'); INSERT INTO tblproducts(productid, product) VALUES (3, …

Read More