Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1evOw6-0007Z4-9Y for pgsql-hackers@arkaria.postgresql.org; Mon, 12 Mar 2018 15:00:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1evOw5-00044j-1k for pgsql-hackers@arkaria.postgresql.org; Mon, 12 Mar 2018 15:00:25 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1evOw4-00044a-Nb for pgsql-hackers@lists.postgresql.org; Mon, 12 Mar 2018 15:00:24 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1evOw1-0005a2-KO for pgsql-hackers@lists.postgresql.org; Mon, 12 Mar 2018 15:00:23 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id w2CF0Jel029253; Mon, 12 Mar 2018 11:00:19 -0400 From: Tom Lane To: Nikita Glukhov cc: =?UTF-8?Q?Darafei_=22Kom=d1=8fpa=22_Praliaskouski?= , Anastasia Lubennikova , pgsql-hackers@lists.postgresql.org Subject: Re: Cast jsonb to numeric, int, float, bool In-reply-to: References: <0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru> <9c013081-5dc0-1b87-a559-a0c2d405727a@postgrespro.ru> <151985418868.6915.17223208131889314092.pgcf@coridan.postgresql.org> <76b000ae-94d1-cd8f-a2e0-e536096469b0@postgrespro.ru> Comments: In-reply-to Nikita Glukhov message dated "Thu, 01 Mar 2018 13:20:29 +0300" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <29251.1520866819.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Mon, 12 Mar 2018 11:00:19 -0400 Message-ID: <29252.1520866819@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Nikita Glukhov writes: > On 01.03.2018 11:19, Darafei "Komяpa" Praliaskouski wrote: >> I would expect some casts to be implicit, so that chaining with other >> functions is possible: > I think that only cast to a numeric type can be made implicit, because > it does not lose precision. I hadn't been following this thread particularly, but I happened to notice this bit, and I thought I'd better pop up to say No Way. There will be *no* implicit casts from json to any numeric type. We have learned the hard way that implicit cross-category casts are dangerous. See the advice in the CREATE CAST man page: It is wise to be conservative about marking casts as implicit. An overabundance of implicit casting paths can cause PostgreSQL to choose surprising interpretations of commands, or to be unable to resolve commands at all because there are multiple possible interpretations. A good rule of thumb is to make a cast implicitly invokable only for information-preserving transformations between types in the same general type category. For example, the cast from int2 to int4 can reasonably be implicit, but the cast from float8 to int4 should probably be assignment-only. Cross-type-category casts, such as text to int4, are best made explicit-only. regards, tom lane