Take a fruits table like below.
| id | name | created_at | updated_at |
|---|---|---|---|
| 1 | apple | 2021-07-01 10:43:13 | 2021-07-01 10:43:13 |
| 2 | orange | 2021-07-01 10:43:13 | 2021-07-01 10:43:13 |
| 3 | strawberry | 2021-07-01 10:43:13 | 2021-07-01 10:43:13 |
Say we wanted to retrieve a comma-separated list of the IDs, we can use the string_agg to achieve this.
SELECT string_agg(id::text, ',') AS ids FROM fruits
This would give you a result set like:
| ids |
|---|
| 1,2,3 |