What's New in PostgreSQL 11: Casting Improvements



    Continuing the topic of the new features of the upcoming release of PostgreSQL 11 (previous posts: one , two , three ), I would like to talk about one small, but important change. Change this applies to casting one type to another. And, of course, it refers to JSONB, because in PostgresPro we love him very much!

    It's about patch c0cbe00f : The essence of the problem is this. If you take PostgreSQL 10, you will find the following behavior: That is, JSONB is not cast to numeric types and boolean. Of course, not that it was a very big problem. In the end, you can always caston through text:

    commit c0cbe00fee6d0a5e0ec72c6d68a035e674edc4cc
    Author: Teodor Sigaev
    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




    =# 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;




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


    Nevertheless, such a solution looks like a crutch, and there are questions about its performance.

    Now let's take a look at the behavior of the master branch: As you can see, you can now cast JSONB into boolean and numeric types. Hurrah! It is characteristic that casting in the opposite direction is only possible through intermediate casting in text: you ask why there is no direct casting? I think it’s just that nobody has gotten their hands on it. Sounds like an opportunity for self-realization , don’t you? ;)

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

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

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






    =# 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)



    Also popular now: