PostgreSQL: Generated column computed from jsonb column



Problem: I am using PostgreSQL 12 new feature Generated Column, but computing column is jsonb type that containing json as "active":true or "active":false. I have tried to create the table using the following PostgreSQL statement.


CREATE TABLE mytable (
    data jsonb,
    active boolean generated always as ((data ->> 'active')::boolean) stored,
    ...
);


The problem is that sometimes active is missing in data so default value save null in the "active" column while I want to default value false.

Solution: When the key does not exist the operator ->> returns NULL, so you can just COALESCE that to the desired default value.

Using COALESCE Set default value if the key (json field) doesn't exist


To resolve the issue just replace the code ((data ->> 'active')::boolean) with COALESCE((data ->> 'public')::boolean, FALSE), look at the below query.


CREATE TABLE mytable (
    data jsonb,
    active boolean generated always as COALESCE((data ->> 'public')::boolean, FALSE) stored,
    ...
);



No comments:

Post a Comment

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...