The following bug has been logged online:

Bug reference: 1494
Logged by: Ludwig Lim
Email address: ludz_lim (AT) yahoo (DOT) com
PostgreSQL version: 8.0.1
Operating system: Linux
Description: psql \df to_char
Details:

Hi:

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?

Version of PostgreSQL:
PostgreSQL 8.0.1 on i686-pc-linux-gnu compiled by GCC 2.96

Thanks in advance,
Ludwig Lim

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Ludwig Lim wrote:
Uh, I see:

test=> \df to_char
List of functions
Schema | Name | Result data type | Argument data types
------------+---------+------------------+-----------------------------------
...
pg_catalog | to_char | text | timestamp without time zone, text

--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings