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

From Adrian Klaver
Subject Re: Looking for a doc section that presents the overload selection rules
Date
Msg-id 4f7a9fe8-ea27-e4a3-34ac-f05fcfb7f5c3@aklaver.com
Whole thread Raw
In response to Re: Looking for a doc section that presents the overload selection rules  (Bryn Llewellyn <bryn@yugabyte.com>)
Responses Re: Looking for a doc section that presents the overload selection rules
List pgsql-general
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.klaver@aklaver.com



pgsql-general by date:

Previous
From: Bryn Llewellyn
Date:
Subject: Re: Looking for a doc section that presents the overload selection rules
Next
From: Bryn Llewellyn
Date:
Subject: Re: Looking for a doc section that presents the overload selection rules