is there any way to define data type on json data in postgreSQL

Issue

I have typescript interface with one property is float and other is string. I can insert json data direcly into table but I am wondering is there any way to define data types in the json data.

interface students{
    percentage: Number;
    name: string;
}

Solution

Sure, you can make a check constraint that does that:

create table student (
    data jsonb check (
        coalesce(jsonb_typeof(data->'name'),'') = 'string' and 
        coalesce(jsonb_typeof(data->'percentage'),'')='number'
    )
);

But why not store the data as columns which are inherently typed instead?

Answered By - jjanes