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

This causes the 42883 error, "function f(date) does not exist". I might've expected the system to have done an implicit conversion to "text" because this conversion is supported, thus:

For some reason, the implicit conversion from "date" to "timestamptz" _is_ considered to be preferable to the implicit conversion from "date" to plain "timestamp".

Where, in the PG doc, can I find a statement of the rules that allow me to predict the outcome of my tests?

On 10/21/21 12:52, Bryn Llewellyn wrote:
> I fear that I'm about to embarrass myself again. So I'll just ask for 
> forgiveness in advance.
> 
> Here's a simple test to get started. (All tests are done in a session 
> where I set the timezone to 'UTC'.)
> 
> *drop function if exists f(text)        cascade;
> **drop function if exists f(timestamp)   cascade;
> **drop function if exists f(timestamptz) cascade;
> **
> *
> *create function f(t in text)
>    returns text
>    language plpgsql
> as $body$
> begin
>    return 'plain "text" overload: '||t;
> end;
> $body$;
> 
> **select f('2021-03-15'::date);
> *
> This causes the 42883 error, "function f(date) does not exist". I 
> might've expected the system to have done an implicit conversion to 
> "text" because this conversion is supported, thus:
> 
> *select f(('2021-03-15'::date)::text);*
> 
> This succeeds with this result:
> 
> *plain "text" overload: 2021-03-15
> *
> There's clearly a rule at work here. For some reason, the implicit 
> conversion from "date" to "text" is not considered to be acceptable.

 From 10.3
"
Look for the best match.

     Discard candidate functions for which the input types do not match 
and cannot be converted (using an implicit conversion) to match. unknown 
literals are assumed to be convertible to anything for this purpose. If 
only one candidate remains, use it; else continue to the next step.
"

See cast query below.


> For some reason, the implicit conversion from "date" to "timestamptz" 
> _is_ considered to be preferable to the implicit conversion from "date" 
> to plain "timestamp".

https://www.postgresql.org/docs/current/catalog-pg-type.html

select oid from pg_type where typname = 'date';
  oid
------
  1082

https://www.postgresql.org/docs/current/catalog-pg-cast.html

select * from pg_cast where castsource = 1082;
   oid  | castsource | casttarget | castfunc | castcontext | castmethod
-------+------------+------------+----------+-------------+------------
  11421 |       1082 |       1114 |     2024 | i           | f
  11422 |       1082 |       1184 |     1174 | i           | f

Note castcontext of 'i'(implicit) and only to timestamp types per below.


select typname, typispreferred from pg_type where oid in (1114, 1184);
    typname   | typispreferred
-------------+----------------
  timestamp   | f
  timestamptz | t


typispreferred  has timestmaptz as preferred cast.

> 
> I started with "38.6. Function Overloading", followed the link to 
> "Chapter 10. Type Conversion" and started with "10.3. Functions". I read 
> "If no exact match is found, see if the function call appears to be a 
> special type conversion request…" as far as "Note that the “best 
> match” rules are identical for operator and function type resolution." 
> So I went to "10.2. Operators" and searched in the page for "timestamp". 
> No hits.
> 
> Where, in the PG doc, can I find a statement of the rules that allow me 
> to predict the outcome of my tests?
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver wrote:

Bryn wrote:

I fear that I'm about to embarrass myself again. So I'll just ask for forgiveness in advance. Here's a simple test to get started. (All tests are done in a session where I set the timezone to 'UTC'.)

drop function if exists f(text)        cascade;
drop function if exists f(timestamp)   cascade;
drop function if exists f(timestamptz) cascade;

create function f(t in text)
  returns text
  language plpgsql
as $body$
begin
  return 'plain "text" overload: '||t;
end;
$body$;

select f('2021-03-15'::date);

This causes the 42883 error, "function f(date) does not exist". I might've expected the system to have done an implicit conversion to "text" because this conversion is supported, thus:

select f(('2021-03-15'::date)::text);

This succeeds with this result:

plain "text" overload: 2021-03-15

There's clearly a rule at work here. For some reason, the implicit conversion from "date" to "text" is not considered to be acceptable.

From 10.3
"
Look for the best match.

Discard candidate functions for which the input types do not match and cannot be converted (using an implicit conversion) to match. unknown literals are assumed to be convertible to anything for this purpose. If only one candidate remains, use it; else continue to the next step.
"

See cast query below.

For some reason, the implicit conversion from "date" to "timestamptz" _is_ considered to be preferable to the implicit conversion from "date" to plain "timestamp".

52.62. pg_type — https://www.postgresql.org/docs/current/catalog-pg-type.html

select oid from pg_type where typname = 'date';

oid
------
1082

52.10. pg_cast — https://www.postgresql.org/docs/current/catalog-pg-cast.html

select * from pg_cast where castsource = 1082;

 oid  | castsource | casttarget | castfunc | castcontext | castmethod
-------+------------+------------+----------+-------------+------------
11421 |       1082 |       1114 |     2024 | i           | f
11422 |       1082 |       1184 |     1174 | i           | f

Note castcontext of 'i'(implicit) and only to timestamp types per below.

select typname, typispreferred from pg_type where oid in (1114, 1184);

  typname   | typispreferred
-------------+----------------
timestamp   | f
timestamptz | t

typispreferred  has timestmaptz as preferred cast.

I started with "38.6. Function Overloading", followed the link to "Chapter 10. Type Conversion" and started with "10.3. Functions". I read "If no exact match is found, see if the function call appears to be a special type conversion request…" as far as "Note that the “best match” rules are identical for operator and function type resolution." So I went to "10.2. Operators" and searched in the page for "timestamp". No hits. Where, in the PG doc, can I find a statement of the rules that allow me to predict the outcome of my tests?

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

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

On 10/21/21 17:42, Bryn Llewellyn wrote:
>> 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:/
>>>>

>>> *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?

Because there is:

to_char ( timestamp with time zone, text ) → text

and date is a subset of timestamp(tz) where timestamptz is the preferred 
form.  This allows you to do things like:

select '10/30/21 22:00:00'::timestamptz - '10/22/21'::date;
     ?column?
-----------------
  8 days 22:00:00

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

Welcome to date/times. As I have said before if you are looking for end 
to end consistency you are in the wrong place. It is an area where 
specific comments are needed to explain your choices.

I am not understanding the template reference as there is a difference 
in output between timestamp and timestamptz.

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

Working in timestamptz is a benefit so I'm fine with that being the 
default.

The bottom line is that working correctly with date/times is difficult 
and requires effort spent studying the underlying structure. More so 
when you switch between systems e.g. Javascript 0 indexed months.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

adrian.klaver@aklaver.com wrote:

Bryn wrote:

adrian.klaver@aklaver.com wrote:

Bryn wrote:

Adrian Klaver wrote:
...

You've lost me entirely here, I'm afraid.
My question was simple: why is there no "to_char ( date, text ) → text" overload?

Because there is:

to_char ( timestamp with time zone, text ) → text

and date is a subset of timestamp(tz) where timestamptz is the preferred form.  This allows you to do things like:

select '10/30/21 22:00:00'::timestamptz - '10/22/21'::date;
   ?column?
-----------------
8 days 22:00:00

Without this, and as long as the good practice rule is followed to code so that implicit conversion is never invoked, then using "to_char()" on a "date" value requires writing an explicit typecast. There are only two possible choices: cast to plain "timestamp" or cast to "timestamptz". And for reasons that I'm sure you'd explain better than I would, the choice makes 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.

Welcome to date/times. As I have said before if you are looking for end to end consistency you are in the wrong place. It is an area where specific comments are needed to explain your choices.

I am not understanding the template reference as there is a difference in output between timestamp and timestamptz.

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

Working in timestamptz is a benefit so I'm fine with that being the default.

The bottom line is that working correctly with date/times is difficult and requires effort spent studying the underlying structure. More so when you switch between systems e.g. Javascript 0 indexed months.

Bryn Llewellyn <bryn@yugabyte.com> writes:
> There could, so easily, have been three “to_char()” overloads for these
> three data types that honored the spirit of the “::text” typecast by
> rendering only what’s meaningful, despite what the template asks for.

You can, of course, trivially make that so in your own database.

=# create function to_char(date, text) returns text
language sql stable strict parallel safe
as 'select pg_catalog.to_char($1::timestamp without time zone, $2)';

=# select to_char(current_date, 'dd-Mon-yyyy TZH:TZM');
      to_char       
--------------------
 22-Oct-2021 +00:00
(1 row)

Regardless of whether the original choice not to have this variant
was intentional or an oversight, I'd be pretty loath to change it now
because of backwards compatibility.  But Postgres is adaptable.

            regards, tom lane

On 10/22/21 10:26 AM, Bryn Llewellyn wrote:
> //

> I’ll make this my final turn on this thread. Yes, I accept that 
> everything to do with the date-time story is tough stuff. And I do 
> understand that this is, to a large extent, just a reflection of the 
> fact that the terrain is inevitably affected by genuine 
> astronomical facts together with the history of technology and human 
> thought. I accept, too, that the PostgreSQL implementation in this space 
> is constrained, to some extent, by decisions taken by the SQL Standard 
> folks, over the years, some of which were arguably questionable. 
> Then PostgreSQL brings its own quirks (esp. e.g. everything to do with 
> intervals and their use). So I do see that application code will need a 
> lot of commenting to make the programmer’s intent clear.
> 
> Having said all this, the following example seems to me to make an 
> unassailable point:
> 
> deallocate all;
> prepare s as
> with c as (
>    select
>      '2021-06-15'         ::date        as   d,
>      '2021-06-15 12:00:00'::timestamp   as   ts,
>      '2021-06-15 12:00:00'::timestamptz as tstz)
> select
>    rpad(current_setting('timezone'), 20)  as "timezone",
>    d   ::text,
>    ts  ::text,
>    tstz::text
> from c;
> 
> \t on
> set timezone = 'Europe/Helsinki';
> execute s;
> 
> set timezone = 'America/Los_Angeles';
> execute s;
> \t
> 
> This is the result:
> 
>   Europe/Helsinki      | 2021-06-15 | 2021-06-15 12:00:00 | 2021-06-15 
> 22:00:00+03
> 
>   America/Los_Angeles  | 2021-06-15 | 2021-06-15 12:00:00 | 2021-06-15 
> 12:00:00-07
> 
> So the “::text” typecast operator understands that “date” values, plain 
> “timestamp” values, and “timestamptz” values each bear different 
> information—and it takes account, in each case, only of the relevant 
> information.
> 
> There could, so easily, have been three “to_char()” overloads for these 
> three data types that honored the spirit of the “::text” typecast by 
> rendering only what’s meaningful, despite what the template asks for. I 
> could write these myself and use “extract()” to get the meaningful 
> elements, and only these, before rendering them, silently refusing the 
> request to render meaningless fields.

Why? You asked for information in the template that is available and it 
provides it.

It does not seem to be that out of line.

 From different realm(Python):

from datetime import date

today_date = date.today()
print(today_date) 
 

2021-10-22

today_date.strftime('%c') 
 

'Fri Oct 22 00:00:00 2021'



It might also be useful to know that to_char() and friends are modeled 
after the Oracle ones:

src/backend/utils/adt/formatting.c

  The PostgreSQL routines for a timestamp/int/float/numeric formatting,
  inspired by the Oracle TO_CHAR() / TO_DATE() / TO_NUMBER() routines.

> 
> However, reality is different. “to_char()” uses defaults when the value 
> at hand doesn’t represent these and then renders them as the template 
> specifies.
> 
> prepare s as
> with c as (
>    select
>      '2021-06-15 12:00:00'::text as t,
>      'dd-Mon-yyyy TZH:TZM'       as fmt)
> select
>    to_char(t::timestamp,   fmt) as "plain timestamp",
>    to_char(t::timestamptz, fmt) as "timestamptz"
> from c;
> 
> This always shows the “TZH:TZM” component of the plain “timestamp” as 
> “00:00”. And for the “timestamptz” value, it shows this to reflect the 
> session’s timezone setting.
> 
> This informs what you get when you want to render a “date” value—and how 
> to code it. Of course, when I do this, I wouldn’t ask to see time-of-day 
> or timezone fields. So fair enough, I suppose. Anyway, so it is—and so 
> it ever will be.
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

You can, of course, trivially make that so in your own database.

=# create function to_char(date, text) returns text
language sql stable strict parallel safe
as 'select pg_catalog.to_char($1::timestamp without time zone, $2)';

=# select to_char(current_date, 'dd-Mon-yyyy TZH:TZM');      to_char       
-------------------- 22-Oct-2021 +00:00
(1 row)

Regardless of whether the original choice not to have this variant
was intentional or an oversight, I'd be pretty loath to change it now
because of backwards compatibility.  But Postgres is adaptable.
			regards, tom lane

create function to_char(dt date, fmt text default 'dd-Mon-yyyy TZH:TZM') returns text

There could, so easily, have been three “to_char()” overloads for these three data types that honored the spirit of the “::text” typecast by rendering only what’s meaningful, despite what the template asks for.

david.g.johnston@gmail.com wrote:

Bryn wrote:

There could, so easily, have been three “to_char()” overloads for these three data types…

The argument about avoiding the implicit cast, and thus being easier for newcomers to figure out, is the compelling one for me. But, frankly, “it just works” applies here - I’ve seen little evidence that there is a meaningful usability issue in the community.

Use a “date” actual for “to_char()”, with no explicit typecast, and with a template that makes sense. In this special case it’s safe to relax the usual rule of practice and just let the implicit typecast have its innocent effect.

> tgl@sss.pgh.pa.us wrote:
>
> Bryn Llewellyn <bryn@yugabyte.com> writes:
>> There could, so easily, have been three “to_char()” overloads for these
>> three data types that honored the spirit of the “::text” typecast by
>> rendering only what’s meaningful, despite what the template asks for.
>
> You can, of course, trivially make that so in your own database.
>
> =# create function to_char(date, text) returns text
> language sql stable strict parallel safe
> as 'select pg_catalog.to_char($1::timestamp without time zone, $2)';
>
> =# select to_char(current_date, 'dd-Mon-yyyy TZH:TZM');
>      to_char
> --------------------
> 22-Oct-2021 +00:00
> (1 row)
>
> Regardless of whether the original choice not to have this variant
> was intentional or an oversight, I'd be pretty loath to change it now
> because of backwards compatibility.  But Postgres is adaptable.

Thanks, Tom. I’d also reached that same conclusion. But I won’t do that. Rather, I’ll advise myself and anyone who asks
meto do what I just wrote in reply to David Johnston. 

On 2021-10-22 10:26:38 -0700, Bryn Llewellyn wrote:
> There could, so easily, have been three “to_char()” overloads for these three
> data types that honored the spirit of the “::text” typecast by rendering only
> what’s meaningful, despite what the template asks for.

I think to_date() is very much intended to produce fixed-width output
even when that makes little sense given the input data. For example:

hjp=> select to_char(3::int, '999.999');
╔══════════╗
║ to_char  ║
╟──────────╢
║    3.000 ║
╚══════════╝
(1 row)

An int has no fractional part, but I asked for 3 digits after the
decimal point so I got them.

hjp=> select to_char(34567::int, '999.999');
╔══════════╗
║ to_char  ║
╟──────────╢
║  ###.### ║
╚══════════╝
(1 row)


34567 doesn't fit into the 3 digits before the point I asked for, so I
just get hashes.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"