0

How to replace ORA_HASH function of Oracle in Postgres? 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.

SELECT DISTINCT ACCNT_TYPE,ACCNT_SUB_TYPE,ACCNT_FROM_VAL,ACCNT_TO_VAL AS T_ACCNT_TO_VAL,
ORA_HASH("NAME"||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

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.

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.

Why this query always gives me null response?

select md5(p.src_id || p.type || p.accountName) 
FROM ACCOUNT p;

If type value is NULL is DB then md5 results in NULL. This is bad.

PAA
  • 1
  • 46
  • 174
  • 282
  • In PostgreSQL you can use `md5()` for it. It is not exact same as ORA_HASH() but it will do almost same thing. – Akhilesh Mishra Oct 28 '20 at 17:47
  • Does this answer your question? [What is the algorithm used by the ORA\_HASH function?](https://stackoverflow.com/questions/45948266/what-is-the-algorithm-used-by-the-ora-hash-function) – Andrew Henle Oct 28 '20 at 17:51
  • I went through all these link before posting the question, please see my updated post . – PAA Oct 28 '20 at 17:52
  • @a_horse_with_no_name - Nope this doesn't solved my query. Please dont close the question. – PAA Oct 28 '20 at 17:57
  • "*Why this query always gives me null response?*" - because one of the columns is null. Oracle behaves differently with regards to empty strings and NULL values. You can use `concat()` instead to treat NULL values like empty strings in Postgres –  Oct 28 '20 at 18:00
  • You can use `coalesce` to replace null values with a default non-null by your choice, and that should work. Depends on what you choose if it will match the same value. – coladict Oct 28 '20 at 18:07
  • @coladict: no need for coalesce. `concat(...)` will work just fine: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=5be8402aefdec615355b911de3ad7d97 –  Oct 28 '20 at 18:30
  • There is `concat_ws()` which accepts&ignores NULL values. – wildplasser Oct 28 '20 at 18:39

1 Answers1

2

If all you need is a hash function for a string, use the PostgreSQL built-in hashtext.

For the concatenation, you could use

hashtext(concat(p.src_id, p.type, p.accountName))
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263