What's New in PostgreSQL 11: Improved Casting

Continuing the theme of the new features of the upcoming release of PostgreSQL 11 (previous posts: one , two , three ), I would like to tell about one small, but important change. This change concerns the casting of one type to another. And, of course, it concerns JSONB, because in PostgresPro we love it very much!

This is a patch c0cbe00f :
')
commit c0cbe00fee6d0a5e0ec72c6d68a035e674edc4cc
Author: Teodor Sigaev <teodor@sigaev.ru>
Date: Thu Mar 29 16:33:56 2018 +0300

Add casts from jsonb

Add explicit cast from scalar jsonb to all numeric and bool types. It would be
better to have cast from scalar jsonb to text too but there is already a cast
from jsonb to text as just text representation of json. There is no way to have
two different casts for the same type's pair.

Bump catalog version

Author: Anastasia Lubennikova with editorization by Nikita Glukhov and me
Review by: Aleksander Alekseev, Nikita Glukhov, Darafei Praliaskouski
Discussion: https://www.postgresql.org/message-id/flat/0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru


The essence of the problem is this. If you take PostgreSQL 10, you'll find the following behavior:

=# select 'true' :: jsonb :: bool;
ERROR: cannot cast type jsonb to boolean
LINE 1: select 'true' :: jsonb :: bool;

=# select '12345' :: jsonb :: int4;
ERROR: cannot cast type jsonb to integer
LINE 1: select '12345' :: jsonb :: int4;

=# select '12345.67' :: jsonb :: float8;
ERROR: cannot cast type jsonb to double precision
LINE 1: select '12345.67' :: jsonb :: float8;


That is, JSONB is not cast to numeric types and boolean. Of course, it’s not really a big deal. In the end, you can always paste through the text:

=# select '12345.67' :: jsonb :: text :: float8;
float8
----------
12345.67


Nevertheless, this decision looks like a crutch, and there are questions about its performance.

Now let's take a look at the behavior of the master branch:

=# select 'true' :: jsonb :: bool;
bool
------
t

=# select '12345' :: jsonb :: int4;
int4
-------
12345

=# select '12345.67' :: jsonb :: float8;
float8
----------
12345.67


As you can see, you can now cast JSONB to boolean and numeric types. Hooray!

It is characteristic that the casting in the opposite direction is so far possible only through intermediate casting in text:

=# select 12345.67 :: jsonb;
ERROR: cannot cast type numeric to jsonb
LINE 1: select 12345.67 :: jsonb;

=# select 12345.67 :: text :: jsonb;
jsonb
----------
12345.67
(1 row)


You ask, why not direct casting? I think it’s just that no one has gotten his hands to finish it. Sounds like an opportunity for self-realization , don't you find? ;)