Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hLV4m-0004mm-Pe for pgsql-docs@arkaria.postgresql.org; Tue, 30 Apr 2019 15:53:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hLV4l-0006bi-Fd for pgsql-docs@arkaria.postgresql.org; Tue, 30 Apr 2019 15:53:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hLUob-0001Pu-3S for pgsql-docs@lists.postgresql.org; Tue, 30 Apr 2019 15:37:05 +0000 Received: from sonic306-20.consmr.mail.ir2.yahoo.com ([77.238.176.206]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hLUoY-0002jc-1r for pgsql-docs@lists.postgresql.org; Tue, 30 Apr 2019 15:37:03 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1556638619; bh=mHKq0n8RBGwppFthHh35K7/gfpasG2AJ1joaG8D94fA=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=NNkw7d1CjDePRY4SnelR25JSHLKJfqApAGGxpxP1cwskdpThsXrkWMAIXsv1DrERMYoTRf8yqmDZ3h8GONARB98YmY7G+gtetCMW7MXVb6XrLjeLK7dp1kFGmtAFX2Q45wn5EUyw8g5C8KkDQsHUbDPyujsVaZ29+H6BUMGtoFeCx/C2xiFPytUOcaCjPjfuPV2Jdvati8pwJqhju8boOoq3pL4brseRGP+QrFXJWBxYTKfgRp8H3xObkQ8gCDG33zQz1M3OQ80RUgsL9W8RaQlMJfvzlktPGvM6rnAWVhFx89q47lhG8C+D7RdD3K5tENL6rOrdy1D6Kk1AkF+86A== X-YMail-OSG: BKTDpmcVM1l8tWiyXLmaBgZCitj00.tzLObHR4CJsFIw3Wam3O09D0YNGyfwfzP Vj59P7SwSxYXakbj.0IRePf8LYsXLtJj.VQXwpOyVJ8tFNXY4w7Sr90AceDKDm9OAjKxCXe2AlIU qzTBgVD.RzQYF.ZiMu4W98s2FO.gceexDKXtrczME8_2vc2YqZoK54STGItc9rnpheRZ28QDJcLp 008QBymQWtE.3HFxnC0861WQO9zDkz0KagtSINGlC1G3mfzDsy3wXiJAEdLeA086w1r.5GYbyL.q FH3DqxFFSMhXxLzv6weQUnZmDf.wmZRMt43UCAmgq6wXTtrQNdsOQoGf8JQFb9S1fvOwtQd3__8V XitcaxxJZZtozJaFMa6LTfxgxpjlSTW0Hv8d6NLt42jcUzbdX67GxH27XcMVXweGQkWJOmP6PRdx TGbNdDBRrrxKFnjRK9PQQ9NxiChfoMyuZ6yXodyVgVq7VzfDPvynPNTawPG5lhE5eMuxPsuNAbCT U67rUmj6utuGc7Sd9jMxupwQoGQpCJnTNf2HSe3PWyEIJ.n8giIa4cyjRug3zz5mF8uY8IzzXxXQ 4EZy9YT9GlQrNeIsamXVWKQjd8dLHTTsL0BsNjmtdkaimnmBmimvKoiCLqPhxe27.g0YHgHpdw0v gcKhrU.f0fCu.Gb_JMnHB3Z6s9oFJl62FVVWXP7_QQIYU86Y0gfXOqvKA1rmagMy5Yk8pAlODtye 6ve26.PGrUDqtkJkb9FzjAzVQFHgnlHZu2IhbqJVTf5YVCOK3GALu8fZQoIoALFvbsU90zSIqqli VgqXwSeVLFQSwvPjg3Dr2YAvrXEMfUIYiz64oqwt0njo1H9z_YnCUoV4_o6bER8JjgowNOfZStII xzcJ4A_nw76tNVzVYLodFVVvK3ZwwY2lcW.qlU3rEM6SfufbjzyWLSBSvBRqal3qgWldfEih4ZB8 2xjyWbblk3a0okdT7dgtBUx73LGTOhfeE9tdTbcTW7oiY.4_jq1bYrkHob.xe4MU67kaJsThwTKM TWsj.cj.9H_R0eF7BNXpFhpUb43xGO3DO4tKEunsqjn1iZZX2hh4iiKN7BM0utgQvF.UVRn5nFv4 RH.kpdwJQCcFJ.YlKUVGnPkMhH0yVZSTXHoTIWcdK7md13_2fgKhxPECOyt31DD0mIx.bHaYh5_2 VgvLtMxOBXzz7 Received: from sonic.gate.mail.ne1.yahoo.com by sonic306.consmr.mail.ir2.yahoo.com with HTTP; Tue, 30 Apr 2019 15:36:59 +0000 Date: Tue, 30 Apr 2019 15:36:59 +0000 (UTC) From: Daniil Treshchin To: "David G. Johnston" Cc: Bruce Momjian , "pgsql-docs@lists.postgresql.org" Message-ID: <1552762048.3960232.1556638619116@mail.yahoo.com> In-Reply-To: References: <155639681797.1368.1856073093791885251@wrigleys.postgresql.org> <20190429231305.ra5z5bapif353vdx@momjian.us> <1767763017.3390110.1556587876525@mail.yahoo.com> <1625110667.3383282.1556589715196@mail.yahoo.com> <1500280966.3430682.1556604977316@mail.yahoo.com> Subject: Re: COALESCE doccumentation MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3960231_1422416194.1556638619114" X-Mailer: WebService/1.1.13554 YMailNorrin Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:66.0) Gecko/20100101 Firefox/66.0 Content-Length: 7782 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_3960231_1422416194.1556638619114 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi David. "You invented the =E2=80=9Chigher=E2=80=9D concept"- False. Not me. That's = Joe Celko p. 227 ISBIN 978-0-12-800761-7. That's the ultimate authority sti= ll living on this earth. "There is not a single character varying typed value in any of these exampl= es" - False. You clearly didn't even look at the examples that I put in these e= mails 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 wrote: =20 =20 On Monday, April 29, 2019, Daniil Treshchin wrote: INTEGER is "higher" than CHARACTER VARYING, so the expressions will be vali= dated to INTEGER and not to CHARACTER VARYING. Why, because the engine pick= ed it this way for you. You invented the =E2=80=9Chigher=E2=80=9D concept; that the engine doesn=E2= =80=99t work this way is the point I=E2=80=99m trying to make.=C2=A0 There = is not a single character varying typed value in any of these examples - an= d since there are integers the outcome will be some kind of number. Here is another example: SELECT COALESCE(NULL, '12', 2,=C2=A0 3 :: INTEGER,= 1 :: NUMERIC, 1 :: INTEGER); Why is the result NUMERIC? Null/unknown; unknown literal; integer; integer with redundant cast; intege= r 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 t= o numeric as well.=C2=A0 All is good at planning time and the query can be = run.=C2=A0 Anyway, the doc still doesn't give any info on these: Tom linked to where this is covered - in the more fundamental SQL concept o= f UNION between multiple queries. PostgreSQL does require explicit casting in places where people would not e= xpect or desire to do so.=C2=A0 The hurdle to be more do-what-I-mean is hig= h.=C2=A0 The material is covered in appropriate chapters early on in the do= cumentation.=C2=A0 Repeating or cross-referencing might be worthwhile but f= ew questions make it to these lists and so the motivation is low.=C2=A0 Thr= eads like this tend to be more effective, or at least easier. David J. =20 =20 ------=_Part_3960231_1422416194.1556638619114 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi David.

"You inv= ented the =E2=80=9Chigher=E2=80=9D concept"
- False.= Not me. That's Joe Celko p. 227 ISBIN 978-0-12-800761-7. That's the ultima= te authority still living on this earth.

"There is not a single character varying typed value in any of th= ese examples"
- False. You clearly didn't e= ven look at the examples that I put in these emails and you're quickly run = to conclusions.

So you're at least wrong in th= ese two points.

Anyway, I give up, you guys de= cide if there is a need for additional doc.

Th= anks.
=20
=20
On Monday, April 29, 2019, 11:53:44 PM PDT, David G. Jo= hnston <david.g.johnston@gmail.com> wrote:


On Monday, April 29, 20= 19, Daniil Treshchin <t.d= nil@yahoo.com> wrote:
INTEGER is "higher"= than CHARACTER VARYING, so the expressions will be validated to INTEGER an= d not to CHARACTER VARYING. Why, because the engine picked it this way for = you.

You invented the =E2=80=9Chigher=E2=80=9D c= oncept; that the engine doesn=E2=80=99t work this way is the point I=E2=80= =99m trying to make.  There is not a single character varying typed va= lue in any of these examples - and since there are integers the outcome wil= l be some kind of number.

Here is another example: SELECT COALESCE(NULL, '12', 2,  3 :: INTEGER, 1 :: NUMERIC, 1 :: INT= EGER); Why is the result NUMERIC?

Null/unknown; unknown literal; integer; integer with redundant cast; = integer cast explicitly to numeric; integer with redundant cast
<= br clear=3D"none">
Integers devolve to numeric implicitly (since = an integer can be represented as a numeric but not the other way around); b= oth unknowns can be coerced to numeric as well.  All is good at planni= ng 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 fundamenta= l SQL concept of UNION between multiple queries.

PostgreSQL does require explicit casting in places where peop= le 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 earl= y on in the documentation.  Repeating or cross-referencing might be wo= rthwhile 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.=

David J.

------=_Part_3960231_1422416194.1556638619114--