We're running HVR 5.3.0/17 (linux_glibc2.5-x64-64bit) and synchronizing Oracle 11 with Postgresql 11 in a test setup. We've ran a refresh from oracle to postgres and all the data has been loaded into postgres just fine. But one of our columns in a table has a varchar2 length of 172 on the oracle side and a varchar length of 50 on the postgres side so the data has been truncated on the postgres side. So far no problem.
However, if we run a compare on that table the compare reports that the tables are "identical" but the data clearly is not, since it has been truncated to 50 chars on the postgres side.Is this normal behaviour? Because I would expect that HVR would report that the tables are not identical.
HVR Compare performs a data comparison, and not a table structure comparison. In heterogeneous environments it is not uncommon for table structures to differ although your example is an obvious case that you would expect to be reported, if table structure validation was part of the comparison.
Is it fair to assume that with your column width 172 on the source that there is data with more than 50 characters in the table? If this is the case, then in order to use HVR to move the data into PostgreSQL you must have instructed HVR to allow the truncation using TableProperties /IgnoreCoerceError or in recent versions TableProperties /CoerceErrorPolicy=<some value> and /CoerceErrorType=STRING_TRUNCATION. Since that would become part of your channel definition it is then also part of the basis for HVR to run the comparison. I.e. you told HVR that you would be OK with string truncation, so we will not flag data differences due to this (as long as, taking string truncation into consideration, data is identical otherwise). If there is no source data exceeding 50 characters then obviously the data validation would also report identical result because no string truncation would have taken place as part of the data movement.
So as a general note HVR compare looks for differences in the data, based on the data flow settings. Table definitions can be different, and you may have even performed transformation along the way. When HVR runs the compare we take all of this in consideration when we run the compare.