title: Why does JSON null not cast to SQL null in postgres?
body: <p>So the following PostgreSQL snippet returns <code>null</code>, as it should:</p>
  
  <pre><code>select ('{"id": null}'::json-&gt;'id')
  </code></pre>
  
  <p>Intuitively, one would expect the following statement to return <code>null</code> or an empty string:</p>
  
  <pre><code>select ('{"id": null}'::json-&gt;'id')::TEXT
  </code></pre>
  
  <p>Instead it returns the string "null". Why?</p>
  
  <p>Additionally,</p>
  
  <pre><code>select ('{"id": null}'::json-&gt;'id')::INTEGER
  </code></pre>
  
  <p>returns <code>cannot cast type json to integer</code> and </p>
  
  <pre><code>select ('{"id": null}'::json-&gt;'id')::TEXT::INTEGER
  </code></pre>
  
  <p>returns <code>invalid input syntax for integer: "null"</code>. (The use case here is casting a JSON null to a SQL null in an INTEGER column.)</p>
  
  <p>There's a <a href="https://stackoverflow.com/questions/32283125/why-cant-null-be-converted-to-jsons-null-in-postgresql">similar question</a> with a somewhat-unintelligible answer that seems to boil down to "JSON nulls and SQL nulls are slightly different" and no further explanation. Can someone help me understand what is going on here? This apparent behavior seems crazy!</p>
  
  <p>How does one get around this cleanly? Testing for string "null" screams of code stink, and refactoring to test every single potential node for null/"null" before casting is equally yuck. Any other ideas?</p>