Postgresql ARRAY_AGG on array only returns first value-CodePudding

In Postgres 10 I'm having an issue converting an integer to a weekday name and grouping all record values via ARRAY_AGG to form a string.

The following subquery only returns the first value in the arrays indexed by timetable_periods.day (which is an integer)

SELECT ARRAY_TO_STRING(ARRAY_AGG((ARRAY['Mon','Tue','Wed','Thu','Fri','Sat','Sun'])[timetable_periods.day]), '-')
FROM timetable_periods
WHERE courses.id = timetable_periods.course_id
GROUP BY timetable_periods.course_id

whereas this shows all days concatenated in a string, as expected:

SELECT ARRAY_TO_STRING(ARRAY_AGG(timetable_periods.day), ', ')
FROM timetable_periods
WHERE courses.id = timetable_periods.course_id
GROUP BY timetable_periods.course_id

E.G. A Course has 2 timetable_periods, with day values 0 and 2 (i.e. Monday and Wednesday) The first query only returns "Tue" instead of "Mon, Wed" (so both an indexing issue and only returning the first day). The second query returns "0, 2" as expected

Am I doing something wrong in the use of ARRAY( with the weeknames? Thanks

Update: The queries above are subqueries, with the courses table in the main query's FROM

CodePudding user response：

You should post correct SQL statements. I suspect a JOIN of courses and timetable_periods, but courses is missing in the FROM clause. Furthermore, both queries contain AND followed by GROUP BY - this will not work.

From your writings I guess you want something like:

select
    c.id,
    string_agg((array['Mon','Tue','Wed','Thu','Fri','Sat','Sun'])[tp.day   1], ', ') as day_names
from
    courses c
    inner join timetable_periods tp on c.id = tp.course_id
group by
    c.id

Your attempts to access the day names array were quite correct. But indexing arrays is 1-based. Concatenating text values can be done with string_agg.