1

I have the following in Postgres 9.2.4:

postgres=# SELECT CHECKSUM(O_ORDERKEY) FROM tpch.orders;
      checksum
--------------------
 322119959934139382
(1 row)

Time: 41437.050 ms

I have an instance of Oracle 11g database, with the same TPCH data, which I want to check consistency with the Postgres instance by comparing table checksums. From this link https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/checksum.html#GUID-3F55C5DF-F23A-4B2F-BC6F-E03B34B78BA8 I found out that CHECKSUM keyword is supported only in Oracle 20c. In Oracle 11g, there are various hash functions supported, such as MD4, MD5, SHA1 etc. Can any one of that be used to get the same functionality as in Postgres checksum()? If yes, how?

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Ahana Pradhan
  • 117
  • 5
  • Do you have a link to the PostgreSQL documentation for the `CHECKSUM` function? I cannot find it and I cannot get `CHECKSUM` to work on [db<>fiddle](https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=b51a8df8f5f5f127fe4e3ad1ec02c509). – MT0 Oct 08 '20 at 09:53
  • 1
    There is no `checksum()` function in Postgres. It seems you have written that yourself, so you should be able to convert that function to PL/SQL –  Oct 08 '20 at 09:56
  • See the approach [here](https://stackoverflow.com/a/33836522/4808122) using the ordered hash calculation and [here](http://www.db-nemec.com/MD5/HashCodeBasedIndentityintheDatabase.html) for the *order independent* hash calculation – Marmite Bomber Oct 08 '20 at 12:35

0 Answers0