postgresql, generated fields from jsonb

Question

I'm using a generated column with postgresql 12 with data from a jsonb field (containing a json with "public":true or "public":false

CREATE TABLE people (
    ...,
    data jsonb,
    public boolean generated always as ((data ->> 'public')::boolean) stored,
    ...
)

sometimes the json (coming from different applications) is missing the public key. how i can set a default to FALSE to public column?

Bergi · Accepted Answer · 2020-04-28 16:08:10Z

1

The ->> operator returns NULL when the key is not found, so you can just COALESCE that to the desired default value:

COALESCE((data ->> 'public')::boolean, FALSE)

answered Apr 28, 2020 at 16:08

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GMB · Accepted Answer · 2020-04-28 16:05:56Z

0

You could use a case expression along with the ? operator, like:

create table people (
    ...,
    data jsonb,
    public boolean generated always as (
        case 
            when data ? 'public' then (data ->> 'public')::boolean
            else false
        end
    ) stored
);

answered Apr 28, 2020 at 16:05

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

postgresql, generated fields from jsonb

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related