How to get day name from weekday number in postgresql?

Question

I have a table with the weekday stored as an integer, 0 to 6.

What function do I need to create the day names relative to these day numbers?

Eg: 0 -> Sun, 1->Mon, etc

Like:

SELECT magic(my_day_number) FROM my_table;
Mon

Answer 1

That's what I would use:

select ('{Sun,Mon,Tue,Wed,Thu,Fri,Sat}'::text[])[extract(dow from now()) + 1] as day_of_week;

Or if you prefer a function:

CREATE OR REPLACE FUNCTION dow_name(p_index integer)
RETURNS text LANGUAGE sql STRICT IMMUTABLE PARALLEL SAFE AS
$$
  SELECT (array['Sun','Mon','Tue','Wed','Thu','Fri','Sat'])[p_index + 1];
$$;

Both options with '{...}'::text[] and array[...] work 8% faster (on Postgres 12) comparing to using CASE block.