ROWCHECKSUM returns a N-bit checksum that returns the same result for the same data on different RDBMS'. It has the same syntax and arguments as CHECKSUM.

Syntax

ROWCHECKSUM(*)
ROWCHECKSUM(col1,...)
ROWCHECKSUM(table)

Arguments

  • CHECKSUM(...) is for aggregations.
  • ROWCHECKSUM(...) is for single row.

Additional Information

  • CHECKSUM uses the Adler32 checksum function.

  • If there is a column with a non-standard or unsupported data type, the value is written as a string before doing any checksum.

  • Postgres MONEY should be converted to float, using MON_MON::numeric::float.

  • BIT and BOOLEAN data types are inconsistent across RDBMS and are not reliably comparable.

Examples

Example tables are drawn from the imdb database, for instance:

  • cast_info (id, person_id, movie_id, person_role_id, note, nr_order, role_id)

  • movie_keyword (id, movie_id, keyword_id)

  • company_name(id, name, country_code, imdb_id, name_pcode_nf, name_pcode_sf)

–All columns, including id. SELECT id, ROWCHECKSUM(*) FROM lms.cirrodb.dbo.company_name ;

–Explicitly named columns exclude id. SELECT id, ROWCHECKSUM(name, country_code, imdb_id, name_pcode_nf, name_pcode_sf) FROM lms.cirrodb.dbo.company_name ;

–CHECKSUM of 380,000 row table, copied to Oracle using DATAMOVE, partitioned by a one dimension. SELECT production_year, CHECKSUM(*) FROM testim.public.aka_title GROUP BY production_year ;

SELECT id, nvchr_10, MASK_FULL(nvchr_10) AS mf, ROWCHECKSUM(nvchr_10) AS nvchr_10c, ROWCHECKSUM(*) AS chk FROM testmy.cirrodb.checksum_data ;

–Create Table As Select (CTAS) with inline functions DROP TABLE testpg.inventory.checksum_target_my ; CREATE TABLE testpg.inventory.checksum_target_my AS SELECT id, nvchr_10, MASK_FULL(nvchr_10) AS mf, ROWCHECKSUM(nvchr_10) AS nvchr_10c, ROWCHECKSUM(*) AS chk FROM testmy.cirrodb.checksum_data ; SELECT * FROM testpg.inventory.checksum_target_my ;