How to SELECT TO_CHAR with list {11:00:00,12:00:00}

Question

I have this table:

postgres::DATABASE=> SELECT * FROM db;
       timeList     |   time   
---------+------------+---------------------
{11:00:00,12:00:00} | 11:22:33
{19:00:00}          | 12:12:33

I need to select timeList values without seconds. But receive this error:

postgres::DATABASE=> SELECT TO_CHAR(timeList, 'HH24:MI') timeList FROM db;

ERROR:  syntax error at or near "timeList"
LINE 1: SELECT TO_CHAR(time, 'HH24:MI') timeList FROM db;
                                        ^

Command to select single value works as expected:

postgres::DATABASE=> SELECT TO_CHAR(time, 'HH24:MI') time FROM db;
 time  
-------
 11:22
 12:12

Answer 1

Quick and dirty

Assuming the column "timeList" is of type time[], there is a quick and dirty way. Casting to varchar(5)[] truncates the string representation of the time so that only HH24:MI remains.

test=> SELECT '{12:12, 1:1:1.123456, 23:59:59.999999}'::time[]::varchar(5)[];
       varchar       
---------------------
 {12:12,01:01,23:59}
(1 row)

db<>fiddle here

It even represents NULL and empty array properly out of the box. Pretty clean for "quick'n'dirty".

Of course, it depends on implementation details of the text representation of time values. But it works with any possible setting I am aware of, and I don't see it changing any time soon. Related:

• https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT

Slow and sure

To format the output any way you want, there is always the slow and sure way that Laurenz provided: unnest, format, aggregate back. But it's typically faster to aggregate back right away with an ARRAY constructor in the LATERAL subquery. This is also guaranteed to keep original order of array elements without keeping track with WITH ORDINALITY:

SELECT *
FROM   db, LATERAL (
   SELECT ARRAY(SELECT to_char(unnest(db.timelist), 'HH24:MI'))
   ) x(times_formatted);

Plus, the aggregation the subquery always produces a row, so it will not eliminate rows with NULL or empty array ({}) silently in the main table (like Laurenz' query does).

The implicit CROSS JOIN still has a side effect: it converts NULL to an empty array ({}).
Can be avoided properly with a LEFT JOIN like this:

SELECT *
FROM   db
LEFT   JOIN LATERAL (
   SELECT ARRAY(SELECT to_char(unnest(db.timelist), 'HH24:MI'))
   ) x(times_formatted) ON db.timelist IS NOT NULL;

Consider the demo in the fiddle.

db<>fiddle here

Related:

• What is the difference between LATERAL and a subquery in PostgreSQL?
• Why is array_agg() slower than the non-aggregate ARRAY() constructor?
• What does [FROM x, y] mean in Postgres?
• Preserve order of array elements after join

Answer 2

Because of your denormalized data model, you have to use unnest and array_agg:

SELECT array_agg(to_char(tl.tle, 'HH24:MI') ORDER BY tl.n),
       to_char(db.time, 'HH24:MI')
FROM db
   CROSS JOIN LATERAL unnest(db.timelist) WITH ORDINALITY AS tl(tle, n)
GROUP BY db;