Looking for a doc section that presents the overload selection rules

On Thursday, October 21, 2021, Bryn Llewellyn <bryn@yugabyte.com> wrote:

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:

Yes, implicit casting to text is bad.

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?

I do not believe the relevant metadata is maintained in the documentation.
You would have to, at minimum, consult the system catalogs; as documented
here:

https://www.postgresql.org/docs/current/typeconv-overview.html

Note that page does discuss the concept of “preference” that you’ve
observed.

David J.

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?

Thank you very much, Adrian. This is just what I needed.

"10.3. Functions" doesn't mention "pg_type". And "52.62. pg_type" doesn't mention "pg_cast". So it's no wonder that I couldn't find what you showed me here. (At least, that's my excuse.)

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.

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 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()"?

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 "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.google.com/url?q=https://www.postgresql.org/docs/8.3/release-8-3.html&amp;source=gmail-imap&amp;ust=1635466561000000&amp;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&amp;source=gmail-imap&amp;ust=1635466561000000&amp;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), 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()"?*

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

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?

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

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

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.

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.

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

Going down that path I would be sorely tempted to default the format
text to the style I typically want.

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

On Friday, October 22, 2021, Bryn Llewellyn <bryn@yugabyte.com> 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.

Even if we added to_char(date…) having it produce output “despite what the
template asks for” is going to be questionable. At least this way we are
honest about what we are doing - if you don’t want non-date stuff in the
output don’t have it in the template. Unless the behavior is going to
differ from what you can get today adding a new function doesn’t have a
benefit. It’s unclear whether having different behavior is desirable.

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.

David J.

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.

Thanks, David. Yes, I agree. This will be my advice:

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 me to 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!"