What's the Equivalent for Listagg (Oracle Database) in Postgresql

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

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"

LISTAGG equivalent in PostgreSQL

Perhaps you want something like this:

string_agg(DISTINCT column_name, ',' ORDER BY column_name)

converting oracle's listagg to postgres

In Postgres, the within group clause is only used for orderd-set aggregate functions - i.e., functions that require an order by clause (e.g., the percentile functions). An optional order by clause, such as string_agg's, goes inside the function's arguments:

SELECT   MAX(eta) eta, mso_id, STRING_AGG(carrier_name, ',' ORDER BY eta) AS carrier 
FROM     tb_flight_schedule
GROUP BY mso_id;

Equivalent of PostgreSQL's array_agg in Oracle XE 11.2

Unless you are using it in a stored procedure to save the output as an array(or collection), a query with LISTAGG should be sufficient and gives the same output.

select ID , LISTAGG(Category,',') WITHIN GROUP ( ORDER BY ID ) Categories
FROM yourtable GROUP BY ID;

In oracle, we do not have have a straightforward conversion function like array_agg. However, you can create a user-defined collection type, then use CAST and COLLECT functions to convert it to a NESTED TABLE to get the same desired output.

Firstly, create a collection TYPE.

create or replace TYPE categorytype as TABLE OF VARCHAR2(20);

Now, running this query is equivalent to using string_agg or LISTAGG, although categories is an array or collection, rather than a string.

select id, CAST ( COLLECT(Category) as categorytype ) categories
FROM yourtable group by id;

| ID | CATEGORIES |
|----|------------|
|  1 |  Cat1,Cat2 |
|  2 |  Cat3,Cat4 |

DEMO

ORACLE connect by to POSTGRESQL conversion

Not sure what the group by nbr is supposed to achieve - as far as I can tell this serves no purpose.

The convoluted connect by level in Oracle can be replaced with a simple generate_series() in Postgres.

So the following will generate 11 timestamp values from 2021-08-11 01:30:00 to 2021-08-11 06:30:00:

select g.dt
from generate_series(timestamp '2021-08-11 01:30:00', 
                     timestamp '2021-08-11 06:30:00', 
                     interval '30 minute') as g(dt)

This can then be aggregated back into a string using string_agg()

select string_agg(to_char(dt, 'dd/mm/yyyy hh24:mi:ss'), ' '), 10 as nbr
from generate_series(timestamp '2021-08-11 01:30:00', 
                     timestamp '2021-08-11 06:30:00', 
                     interval '30 minute') as g(dt)

If you need the number of rows generated, you can use the with ordinality clause to get that:

select string_agg(to_char(dt, 'dd/mm/yyyy hh24:mi:ss'), ' '), max(idx) as nbr
from generate_series(timestamp '2021-08-11 01:30:00', 
                     timestamp '2021-08-11 06:30:00', 
                     interval '30 minute') with ordinality as g(dt,idx)

---

Related Topics