title: Postgres Jsonb datatype
body: <p>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 <strong>data</strong> is single column. Consider I get json in below format -</p>
  
  <pre><code>{
    "CountActual": 1234,
    "CountActualCharacters": "thisreallyworks!"
    "Date": 09-11-2001
  }
  </code></pre>
  
  <p>Correct datatype of above json:- <code>number(10)</code>, <code>varchar(50)</code>, <code>date</code></p>
  
  <p>Now to put validations on I'm using constraints</p>
  
  <p><strong><em>Query 1 -</em></strong></p>
  
  <pre><code>ALTER TABLE public."Detail"
      ADD CONSTRAINT "CountActual" 
      CHECK ((data -&gt;&gt; 'CountActual')::bigint &gt;=0 AND length(data -&gt;&gt; 'CountActual') &lt;= 10);
  </code></pre>
  
  <p>--Working fine.</p>
  
  <p>But for <strong><em>Query 2</em></strong>-</p>
  
  <pre><code>ALTER TABLE public."Detail"
      ADD CONSTRAINT "CountActualCharacters" 
      CHECK ((data -&gt;&gt; 'CountActualCharacters')::varchar &gt;=0 AND length(data -&gt;&gt; 'CountActualCharacters') &lt;= 50);
  </code></pre>
  
  <p>I'm getting below error -</p>
  
  <pre><code>[ERROR: operator does not exist: character varying &gt;= integer
  HINT: No operator matches the given name and argument type(s). 
  You might need to add explicit type casts.]
  </code></pre>
  
  <p>I tried another way also like -</p>
  
  <pre><code>ALTER TABLE public."Detail"
      ADD CONSTRAINT CountActualCharacters CHECK (length(data -&gt;&gt; 'CountActualCharacters'::VARCHAR)&lt;=50)
  </code></pre>
  
  <p>Above constraints works successfully but I don't think this is the right way as my validation is not working when inserting the data -</p>
  
  <pre><code>Insert into public."Detail" values ('{" 
    CountActual":1234,
    "CountActualCharacters":789
    "Date": 11-11-2009
  }');
  </code></pre>
  
  <p>And its shows insert successfully when passing in 789 in CountActualCharacters instead of varchar like "the78isgood!". </p>
  
  <p>So please can anyone suggest me proper constraint for PostgreSQL for <code>varchar</code> just like <code>number</code> that I have written  in Query 1.</p>
  
  <p>And if possible for Date type also with <code>DD-MM-YYYY</code> format.</p>
  
  <p>I just started with PostgresSQL, forgive me if I'm sounded silly but I'm really stuck here.</p>