Re: Looking for a doc section that presents the overload selection rules - Mailing list pgsql-general

> On 21-Oct-2021, at 17:15, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> 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
"Table52.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
codereaders. 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.google.com/url?q=https://www.postgresql.org/docs/8.3/release-8-3.html&source=gmail-imap&ust=1635466561000000&usg=AOvVaw1Cm9kd4XZPydsVQ0qGU2a-
>
> 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.google.com/url?q=https://www.postgresql.org/docs/14/functions-formatting.html&source=gmail-imap&ust=1635466561000000&usg=AOvVaw1VLjGNdZOaBaaAolnnrXtx
>
> 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),
thenall 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
abouttimezones), 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()"?*

You've lost me entirely here, I'm afraid.

My question was simple: why is there no "to_char ( date, text ) → text" overload?

Without this, and as long as the good practice rule is followed to code so that implicit conversion is never invoked,
thenusing "to_char()" on a "date" value requires writing an explicit typecast. There are only two possible choices:
castto plain "timestamp" or cast to "timestamptz". And for reasons that I'm sure you'd explain better than I would, the
choicemakes no actual difference to the outcome when a template is used that's natural for a "date" value. 

So the consequence is that you have to write cluttered code and a fairly elaborate comment to say what your intention
is.

What would you do here? Would you break the rule of practice so that you simply invoke "to_char()" on a "date" value
_withoutwriting a typecast_ and then letting the implicit conversion (which we know is to "timestamptz") have its
innocenteffect?