Hi, One of our customers has this workload where every so often they update the whole table to make sure it's up-to-date. In general, you'd probably want to use MERGE for such a workload and ignore all rows that already have only matching data, but there's a catch: PostgreSQL doesn't have an efficient way to check if the provided data is actually equal in all senses of the word, so we can't easily and cheaply determine whether an update is needed; which is one reason why the full table was updated every time.
A naive approach to determining whether each value needs to be updated would use `old IS NOT DISTINCT FROM new`, but a.) this relies on `=` operators to exist for that type, and b.) the = operator of some types don't always distinguish between values that are different for human readers; with as famous example '1.0' and '1.00' in numeric; they have an equal value but are clearly distinct to readers (and certain functions). One could get around this in this case by 'simply' casting to text and comparing the outputs (using the C collation for performance and determinism), or by wrapping it in a row (which then uses record_image_eq, which does use binary compare functions internally), but both imply additional parsing, wrapping, and overhead compared to a direct datum_image_eq call. So, attached is a simple and to-the-point patch that adds the function mentioned in $subject, which will tell the user whether two values of the same type have an exactly equal binary representation, using datum_image_eq. Kind regards, Matthias van de Meent
v1-0001-Add-SQL-level-datum-equality-tests.patch
Description: Binary data
