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. — user330315
– user330315, Commented Aug 29, 2018 at 17:11
Sounds like a case of unnecessary JSON. If you really need data type validation, then use regular columns with the correct data type. — user330315
– user330315, Commented Aug 29, 2018 at 17:13
that was my bad. Right now, I need to validate the contents of input json. — Ankur
– Ankur, Commented Aug 29, 2018 at 17:14
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? — user330315
– user330315, Commented Aug 29, 2018 at 17:16

Alexey Soshin · Accepted Answer · 2018-08-29 17:31:18Z

2

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

edited Aug 29, 2018 at 17:31

user330315

answered Aug 29, 2018 at 17:18

Alexey Soshin

17.9k2 gold badges37 silver badges42 bronze badges

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

3 Comments

Ankur Over a year ago

Thanks alexey, but in that article there is no mention of varchar, and if i use string then blankspaces and whitespaces are also included in json will passed the validation. I need this "thu45io89" to validate not "ths 097 ndkn lp". :)

Alexey Soshin Over a year ago

You're confusing JSON types with DB types. They are different. And not accepting spaces is your business logic, not something a type would validate.

user330315 Over a year ago

@Ankur: you should edit your question and add the check constraint that you currently use in Oracle

Collectives™ on Stack Overflow

Postgres Jsonb datatype

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related