1

I need to convert a query from Oracle SQL to Postgres. The query returns in a single row elements from a cursor. I'll write a simplified form of this query in Oracle:

select CONCAT_LIST( cursor( select first_name || '-' || last_name from sys_users)  ,',') as users_list from dual

If the table "sys_users" has 2 elements:

1. first name - "John", last name - "Smith"
2. first name - "George", last name - "Doe" ,

the result is:

John-Smith,George-Doe

I need to get the same result in Postgres, if the elements in this table are the same.

Catalin Vladu
  • 389
  • 1
  • 6
  • 17

1 Answers1

2

To aggregate strings, use string_agg()

select string_agg(first_name || '-' || last_name, ',') as users_list
from sys_users;

Note that if first_name or last_name can be null, the result of the concatenation will also be null (unlike in Oracle). To get around that, use concat() instead which simply treats NULL as an empty string:

select string_agg(concat(first_name, '-', last_name), ',') as users_list
from sys_users;

Or even better: use concat_ws() which will leave out the '-' if an element is NULL:

select string_agg(concat_ws('-', first_name, last_name), ',') as users_list
from sys_users;

Btw: your Oracle query is overly complicated, you can essentially use the same as the first Postgres query if you replace string_agg() with listagg()

select listagg(firstname||'-'||lastname, ',') within group (order by lastname)
from sys_users;