postgresql - Get day name based on custome ID of bigint[] datatype

Question

I want to display Day name based on ID which is in bigint[] in the table as shown below:

Table:

create table tbl_days
(
day_ids bigint[]
);

Records:

insert into tbl_days values('{1,2}');
insert into tbl_days values('{1,2,3}');
insert into tbl_days values('{1,4}');
insert into tbl_days values('{4,7}');
insert into tbl_days values('{1,2,3,4,5,6,7}');
insert into tbl_days values('{2,4,7}');

Would like to display day name for:

1 for Monday 2 for Tuesday . .. 7 for Sunday.

Query 1: Using replace(), which is taking 3 more seconds to get the main query result.

select replace(replace(replace(replace(replace(replace(replace(day_ids::varchar,'1','Monday'),'2','Tuesday'),'3','Wednesday'),'4','Thursday'),'5','Friday'),'6','Saturday'),'7','Sunday') 
from tbl_days;

Query 2: Using string_agg(), here problem with an order.

Step 1: Add days into temp table

create temp table temp_days
(
id int,
days varchar
);

insert into temp_days values(1,'Monday'),(2,'Tuesday'),(3,'Wednesday'),(4,'Thursday'),(5,'Friday'),(6,'Saturday'),(7,'Sunday');

Step 2: Join with main table

select d.day_ids,string_agg(distinct t.days,',') 
from tbl_days d 
inner join temp_days t on t.id = any(d.day_ids)
group by d.day_ids

标签： postgresqlpostgresql-9.5

Answer 1

step-by-step demo:db<>fiddle

SELECT
    id,
    string_agg(                                               -- 4
        CASE day                                              -- 3
            WHEN 1 THEN 'Monday'
            WHEN 2 THEN 'Tuesday'
            WHEN 3 THEN 'Wednesday'
            WHEN 4 THEN 'Thursday'
            WHEN 5 THEN 'Friday'
            WHEN 6 THEN 'Saturday'
            WHEN 7 THEN 'Sunday'
        END,
        ','
        ORDER BY index_in_array                               -- 4
    ) 
FROM (
    SELECT
        *,
        row_number() OVER () as id                            -- 1
    FROM tbl_days
) s,
unnest(day_ids) WITH ORDINALITY as t(day, index_in_array)     -- 2
GROUP BY id

1. For my approach you need an id column. This creates one. If you already have one, you can ignore this step
2. unnest() expands the array into one row per element. The WITH ORDINALITY clause adds an index to the records which saves the position of the element in the original array
3. Replace the numbers with the related string using a CASE clause
4. Reaggregate the weekdays by their original ids. The order can be ensured by using the in (2) created index, which can be using in the ORDER BY clause of the aggregate