BUG #1494: psql \df to_char

Ludwig Lim wrote:

The following bug has been logged online:

Bug reference: 1494
Logged by: Ludwig Lim
Email address: ludz_lim@yahoo.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

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@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

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