On Sunday, August 3, 2025, Corey Huinker <
corey.huinker@gmail.com> wrote:
hi.
one more question:
For binary coercible type casts, no formatted related function for it,
should we error out?
For example, should the following error out or return text '1'.
select cast('1'::text as text format 'YYYY'::text);
I'm hoping the standard says (or allows us to) error out here.
We have some influence in that, I believe.
text as a type has no semantics on which to associate a format so it should be an error to attempt to do so. Not a silent no-op.
+1
I was under the impression that for format to be allowed in the expression one of the two data types involved has to be text and the other must not be text.
I hadn't understood that, but also hadn't thought of a case where it might be wanted until just now. What if someone wanted a cast from JSONB to their custom type, and the format was a specific keypath to extract from the JSONB? It's true that could be accomplished by first extracting the keypath and then CASTing that expression, but the same is true for text->date, regexing a YYYY-MM-DD into the locale default.
Feels like the same basic answer. Create cast has a single (because it’s one-way) function accepting between 1 and 3 arguments. Change it to accept between 1 and 4 arguments and the 4th is where the format expression gets passed. If a format expression is present and the function doesn’t have a 4th argument, error.
But, once this is implemented, does it then make sense to then parse to_char() and to_date() into casts?
I have no principled reason but I wouldn’t bother to turn these calls into casts nor do I think turning casts into these specific function calls by name is a good idea. Leave the legacy stuff in place for compatibility, unchanged from its present form, and do the new stuff anew.
David J.
