title: How to replace ORA_HASH function of Oracle in Postgres?
body: <p>How to replace <strong>ORA_HASH</strong> function of Oracle in <strong>Postgres</strong>?  I am looking to implement the Batch and merge logic thats been written into Oracle and same I am looking to implement it into Postgres.</p>
  <pre><code>SELECT DISTINCT ACCNT_TYPE,ACCNT_SUB_TYPE,ACCNT_FROM_VAL,ACCNT_TO_VAL AS T_ACCNT_TO_VAL,
  ORA_HASH(&quot;NAME&quot;||TO_CHAR(LIFECYCLE_DATE,'DD-MM-YYYY HH24:MI:SS')||LEGACY_ICA_TYPE||TO_CHAR(PURGE_DATE,'DD-MM-YYYY HH24:MI:SS')
  ||HUB_STATE_IND||LIFECYCLE_STATUS_CD||VAT_ID||LICENSED_SW||PRIMARY_ICA||TOKEN_ACCT_SRV_DESC) AS HASH_VAL
  FROM C_ACCNT
  </code></pre>
  <p>All the fields mentioned in the ORA_HASH is used to evaluate if INSERT should be done UPSERT should be done by considering all these fields.</p>
  <p>Almost the same query but table name is different doing the left outer join. Also if the HASH VALUE is different then it would be considered for UPSERT.</p>
  <p>Why this query always gives me null response?</p>
  <pre><code>select md5(p.src_id || p.type || p.accountName) 
  FROM ACCOUNT p;
  </code></pre>
  <p>If type value is NULL is DB then md5 results in NULL. This is bad.</p>