String concatenation using operator "||" or format() function

Question

Should we rather use the concatenation operator || or the format() function in trigger functions?

Is there any advantage of using one over the other or does it come down to personal preference and readability?

Would you say for simple concatenation, use the operator but for more complex concatenation, using the format function?

Tracey

Answer 1

There are basically 4 standard tools for concatenating strings. Simplest / cheapest first:

|| (the standard SQL concatenation operator) ...

• returns null if any operand is null. (May or may not be desirable.)
• is a bit faster than format() or concat().
• allows shortest syntax for very few strings to concatenate.
• is more picky about input types as there are multiple different || operators, and the input types need to be unambiguous for operator type resolution.
• concatenating string-types is IMMUTABLE, which allows their safe use in indexes or other places where immutable volatility is required.

concat() ...

• does not return null if one argument is null. (May or may not be desirable.)
• is less picky about input types as all input is coerced to text.
• allows shortest syntax for more than a couple of strings to concatenate.
• has only function volatility STABLE (because it takes "any" input type and coerces the input to text, and some of these conversions depend on locale or time-related settings). So not suitable where immutable volatility is required. See:
  • CONCAT used in INDEX causes ERROR: functions in index expression must be marked IMMUTABLE

concat_ws() ("with separator") ...

• allows shortest syntax when concatenating strings with separators.
• only inserts a separator for not-null strings, simplifying that particular (frequent) case a lot.
• is otherwise like concat().

format() ...

• allows for readable, short code when concatenating variables and constants.
• provides format specifiers to safely and conveniently quote stings and identifiers (to defend against SQL injection and syntax errors), making it the first choice for dynamic SQL. (You mention trigger functions, where a lot of dynamic SQL is used.)
• is the most sophisticated tool. You can reuse the same input multiple times (with different quotation using different format specifiers).
• does also not return null if any of the input parameters are null. (May or may not be desirable.)
• also has only volatility STABLE.

Further reading:

• Combine two columns and add into one new column
• How to concatenate columns in a Postgres SELECT?
• Insert text with single quotes in PostgreSQL
• SQL syntax: Concatenating multiple columns into one

Medicament