Re: BUG #1494: psql \df to_char

Ludwig Isaac Lim wrote:
>
> --- Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073