Actually Postgres documents this behavior:
https://www.postgresql.org/docs/15/functions-string.html text || anynonarray → text anynonarray || text → text Converts the non-string input to text, then concatenates the two strings. (The non-string input cannot be of an array type, because that would create ambiguity with the array || operators. If you want to concatenate an array's text equivalent, cast it to text explicitly.) So in Postgres concatenation always converts arguments to VARCHAR, which strips the trailing spaces. Mihai From: mbu...@gmail.com Sent: Thursday, June 29, 2023 5:28 PM To: dev@calcite.apache.org Subject: Concatenating CHAR(N) and VARCHAR strings Hello, What happens when you concatenate a CHAR(N) and a VARCHAR value? Calcite seems to insert no casts before concatenation, so the trailing spaces for the CHAR(N) value are preserved. However, Postgres has the following test: https://github.com/postgres/postgres/blob/5bcc7e6dc8cb6fb6ab16c118a1e710fc85 096f76/src/test/regress/expected/strings.out#L1802C1-L1805C21 SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown type"; Concat char to unknown type ----------------------------- characters and text The same test (adapted for the Babel syntax) evaluates statically in Calcite to: "characters and text” It looks like Postgres implicitly casts the CHAR(20) value to TEXT before concatenation. I have stared at tables with type conversions in various databases for a long time, but I could not figure out what the result is supposed to be. Thank you, Mihai