Postgres Jsonb datatype

Question

I am using PostgreSQL to create a table based on json input given to my Java code, and I need validations on JSON keys that is passed on the database just like oracle but problem here is the whole jsonb datatype column name lets say data is single column. Consider I get json in below format -

{
  "CountActual": 1234,
  "CountActualCharacters": "thisreallyworks!"
  "Date": 09-11-2001
}

Correct datatype of above json:- number(10), varchar(50), date

Now to put validations on I'm using constraints

Query 1 -

ALTER TABLE public."Detail"
    ADD CONSTRAINT "CountActual" 
    CHECK ((data ->> 'CountActual')::bigint >=0 AND length(data ->> 'CountActual') <= 10);

--Working fine.

But for Query 2-

ALTER TABLE public."Detail"
    ADD CONSTRAINT "CountActualCharacters" 
    CHECK ((data ->> 'CountActualCharacters')::varchar >=0 AND length(data ->> 'CountActualCharacters') <= 50);

I'm getting below error -

[ERROR: operator does not exist: character varying >= integer
HINT: No operator matches the given name and argument type(s). 
You might need to add explicit type casts.]

I tried another way also like -

ALTER TABLE public."Detail"
    ADD CONSTRAINT CountActualCharacters CHECK (length(data ->> 'CountActualCharacters'::VARCHAR)<=50)

Above constraints works successfully but I don't think this is the right way as my validation is not working when inserting the data -

Insert into public."Detail" values ('{" 
  CountActual":1234,
  "CountActualCharacters":789
  "Date": 11-11-2009
}');

And its shows insert successfully when passing in 789 in CountActualCharacters instead of varchar like "the78isgood!".

So please can anyone suggest me proper constraint for PostgreSQL for varchar just like number that I have written in Query 1.

And if possible for Date type also with DD-MM-YYYY format.

I just started with PostgresSQL, forgive me if I'm sounded silly but I'm really stuck here.

What do you mean with "Just like Oracle"? Oracle can only validate if the JSON is structurally valid, nothing more. — a_horse_with_no_name
Sounds like a case of unnecessary JSON. If you really need data type validation, then use regular columns with the correct data type. — a_horse_with_no_name
that was my bad. Right now, I need to validate the contents of input json. — Ankur
Your check constraint validates that not more than 50 characters can be stored for the key CountActualCharacters just like a varchar(50) would do. Why do you expect it to fail when storing 3 characters like 789? — a_horse_with_no_name

Alexey Soshin Alexey Soshin · Accepted Answer · 2018-08-29T17:18:08

You can use jsonb_typeof(data -> 'CountActualCharacters') = 'string'

Note the single arrow, as ->> will try to convert anything to string.

You can read more about JSON functions in PostgreSQL here: https://www.postgresql.org/docs/current/static/functions-json.html

Postgres Jsonb datatype

1 Answers