is there a function to concatenate all columns in teiid?

Question

I am trying to do a checksum for change detection on rows, so I can compare 2 tables. One way I have done in the past is by concatenation of all columns and then do a hash. How can I concat all columns?

SELECT concat(*) form TABLE;

does not work

I also tried the PostgreSQL variant of

select r::text from sandbox.abs_frmt r;

Possible duplicate? https://stackoverflow.com/questions/38857363/concatenating-all-columns-of-each-record-into-one-entry — Xiaoyu Lu, Oct 29 '18 at 14:34

score 0 · Answer 1 · answered Nov 06 '18 at 16:35

By default string lengths are limited, so it's probably best to do the hash of each column and use a function to combine the hashes.

If you don't have a concern with the size of the concatted value, the closest built-in logic would be to use jsonArray, jsonObject, or xmlforest to create a single vale that represents a row - jsonArray may be preferable as it will just be the values, no names:

select sha2_256(cast(jsonArray(col1, col2, ...) as string)),... from tbl;

is there a function to concatenate all columns in teiid?

1 Answers1