Re: BUG #1494: psql \df to_char

Ludwig Isaac Lim wrote:
>
> --- Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>
> > >   I notice that when I type \df to_char in psql prompt
> > the following does
> > > not appear as one of the possible arguments of to_char:
> > >
> > >      time without time zone,text
> > >
> > > However, the following select statement works:
> > >
> > >    SELECT to_char(current_time :: 'time without time
> > zone', 'ss');
> > >
> > >    Is this a bug?
...
> ------------+---------+------------------+-----------------------------------
> >      ...
> >      pg_catalog | to_char | text             | timestamp
> > without time zone, text
> >
>
>   I did saw that, but I think was thinking "time without
> time zone" is not the same as "timestamp without time
> zone". I was just thinking that it might be misleading to
> some person.

Oh, I missed that you were saying "time" and not "timezone".  Hmm.

Seems current_timestamp has a timezone and that fails:

    test=> SELECT to_char(current_time, 'hh');
    ERROR:  function to_char(time with time zone, "unknown") does not exist
    HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

as does 'time with time zone':

    test=> SELECT to_char(current_time :: time with time zone, 'hh');
    ERROR:  function to_char(time with time zone, "unknown") does not exist
    HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

but with _no_ time zone it works:

    test=> SELECT to_char(current_time :: time without time zone, 'hh');
     to_char
    ---------
     08
    (1 row)

    test=> SELECT to_char(current_time :: time, 'hh');
     to_char
    ---------
     08
    (1 row)

The reason to_char() works is because of the auto-conversion from time
(no tz) to interval (1083 is the 'time' data type):

    test=> select typname from pg_cast, pg_type where castsource = 1083 and
    casttarget = pg_type.oid;
     typname
    ----------
     text
     bpchar
     varchar
     time
     interval   <----
     timetz
    (6 rows)

and you will see to_char() has an interval match:

    test=> \df to_char
                                  List of functions
       Schema   |  Name   | Result data type |        Argument data types
    ------------+---------+------------------+-----------------------------------

     pg_catalog | to_char | text             | bigint, text
     pg_catalog | to_char | text             | double precision, text
     pg_catalog | to_char | text             | integer, text
     pg_catalog | to_char | text             | interval, text
     pg_catalog | to_char | text             | numeric, text
     pg_catalog | to_char | text             | real, text
     pg_catalog | to_char | text             | timestamp with time zone text
     pg_catalog | to_char | text             | timestamp without time zone, text
    (8 rows)

and I see that working below:

    test=> SELECT to_char(current_time :: time :: interval, 'hh');
     to_char
    ---------
     08
    (1 row)

So, basically what is happening is the time is being stripped of the
timezone, then the time is being converted to just hours/minutes/seconds
(an interval) and then passed to to_char().

With that analysis, I think you can see why the 'time zone' doesn't work
for this case.

So, in fact here is no literl 'time without time zone' match in
to_char() but rather an automatic type conversion.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073