CODEWITHSUNDEEP
sqlpostgresqlstring-aggregationlistagg
cy221
cy221

Reputation: 1147

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

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?

Upvotes: 50

Views: 93093

Answers (2)

Vivek S.
Vivek S.

Reputation: 21905

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"

Upvotes: 62

Akash Reddy
Akash Reddy

Reputation: 241

From Postgres 9.0 or later, I believe you could do something like this:

SELECT c_id, STRING_AGG(c_grp_id, ',' ORDER BY c_grp_id) AS group_ids 
FROM c_grp_at 
GROUP BY c_id

Upvotes: 20

Related Questions