Re: Looking for a doc section that presents the overload selection rules

On 10/21/21 15:45, Bryn Llewellyn wrote:
>> /Adrian Klaver wrote:/
>>
>>> /Bryn wrote:/
>>>

> Thanks, too, to David Johnston for your reply. Yes, I see now that the
> "10.1. Overview" page that starts the "Type Conversion" chapter does
> have lots of inflexions of the verb "prefer". And close to one of these
> there's a link to "Table 52.63" on the "52.62. pg_type" page. But I
> failed to spot that.
>
> You said "implicit casting to text is bad". Yes, all implicit casting
> is, at best, potentially confusing for human code readers. I aim
> religiously to avoid this and always aim to use an explicit typecast
> instead.

This was explicitly dealt with in the Postgres 8.3 release:

https://www.postgresql.org/docs/8.3/release-8-3.html

E.24.2.1. General

Non-character data types are no longer automatically cast to TEXT
(Peter, Tom)

>
> And this brings me to what started me on this path today. "\df to_char"
> shows that while it has overloads for both plain "timestamp" and
> "timestamptz" date-time inputs, it has no "date" overload. Here's a

That is because:

https://www.postgresql.org/docs/14/functions-formatting.html

to_char ( timestamp, text ) → text
to_char ( timestamp with time zone, text ) → text
Converts time stamp to string according to the given format.
to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS') → 05:31:12

to_char() expects a timestamp and per my previous post the preferred
cast for a date to a timestamp is to timestamptz.

> contrived test:
>
> deallocate all;
> prepare s as
> with c as (
>   select
>     '2021-06-15'::date as d,
>     'dd-Mon-yyyy TZH:TZM' as fmt)
> select
>   rpad(current_setting('timezone'), 20)  as "timezone",
>   to_char(d,              fmt)           as "implicit cast to timestamptz",
>   to_char(d::timestamptz, fmt)           as "explicit cast to timestamptz",
>   to_char(d::timestamp,   fmt)           as "explicit cast to plain
> timestamp"
> from c;
>
> \t on
> set timezone = 'Europe/Helsinki';
> execute s;
>
> set timezone = 'America/Los_Angeles';
> execute s;
> \t off
>
> It gives the result that I'd expect:
>
>  Europe/Helsinki      | 15-Jun-2021 +03:00           | 15-Jun-2021
> +03:00           | 15-Jun-2021 +00:00
>  America/Los_Angeles  | 15-Jun-2021 -07:00           | 15-Jun-2021
> -07:00           | 15-Jun-2021 +00:00
>
> And, given that nobody would include "TZH:TZM" in the template for
> rendering a date (except in this contrived test), then all three text
> renderings in this test would be identical.
>
> However, it seems to me that the proper practice must be not to rely on
> intellectual analysis and the implicit cast. Rather, you must say that
> "date" is more like plain "timestamp" than it's like "timestamptz" (in
> that it knows nothing about timezones), and to write the explicit cast
> to plain "timestamp". But this leads to nastily cluttered code.
>
> *Why is there no "date" overload of "to_char()"?*
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com