Hi David.
"You invented the “higher” concept"
-
False. Not me. That's Joe Celko p. 227 ISBIN 978-0-12-800761-7. That's the ultimate authority still living on this earth.
"There is not a single character varying typed value in any of these examples"
- False. You clearly didn't even look at the examples that I put in these emails and you're quickly run to conclusions.
So you're at least wrong in these two points.
Anyway, I give up, you guys decide if there is a need for additional doc.
Thanks.
On Monday, April 29, 2019, 11:53:44 PM PDT, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Monday, April 29, 2019, Daniil Treshchin <
t.dnil@yahoo.com> wrote:
INTEGER is "higher" than CHARACTER VARYING, so the expressions will be validated to INTEGER and not to CHARACTER VARYING. Why, because the engine picked it this way for you.
You invented the “higher” concept; that the engine doesn’t work this way is the point I’m trying to make. There is not a single character varying typed value in any of these examples - and since there are integers the outcome will be some kind of number.
Here is another example: SELECT COALESCE(NULL, '12', 2, 3 :: INTEGER, 1 :: NUMERIC, 1 :: INTEGER); Why is the result NUMERIC?
Null/unknown; unknown literal; integer; integer with redundant cast; integer cast explicitly to numeric; integer with redundant cast
Integers devolve to numeric implicitly (since an integer can be represented as a numeric but not the other way around); both unknowns can be coerced to numeric as well. All is good at planning time and the query can be run.
Anyway, the doc still doesn't give any info on these:
Tom linked to where this is covered - in the more fundamental SQL concept of UNION between multiple queries.
PostgreSQL does require explicit casting in places where people would not expect or desire to do so. The hurdle to be more do-what-I-mean is high. The material is covered in appropriate chapters early on in the documentation. Repeating or cross-referencing might be worthwhile but few questions make it to these lists and so the motivation is low. Threads like this tend to be more effective, or at least easier.