String Concatenation and Formatting in SQL

PostgreSQL has three ways to join strings together — the || operator, the CONCAT family, and FORMAT — and the choice between them matters whenever a column might be NULL.

You're building a display label for each customer: their name and country, formatted for a report. Some customers are missing a country. Whether the result is NULL, partial, or gracefully filled depends entirely on which tool you use.

|| is the simplest: it concatenates two values with a binary operator. But it follows the standard NULL propagation rule — if either side is NULL, the result is NULL. A missing country means the entire label comes back NULL:

If country is NULL for any customer, that row returns NULL for customer_label. No error — just a missing value where you expected a string.

CONCAT fixes this. It accepts any number of arguments and treats NULL inputs as empty strings:

If last_name is NULL, CONCAT skips it and returns just the first name. This is usually what you want when building display values from optional fields.

CONCAT_WS (concatenate with separator) takes a separator as its first argument and joins all non-NULL values with that separator between them — no doubled separators, no trailing delimiter:

If middle_name is NULL, the output is 'Smith, John' — not 'Smith, John, '. The separator only appears between values that actually exist.

FORMAT works from a template. You write a string with %s placeholders and provide the values to fill them in order:

FORMAT coerces each argument to text automatically, so you don't need to cast numbers or dates first. NULL arguments are inserted as empty strings. FORMAT is especially useful when building a label with fixed structure — if you find yourself writing long || chains with multiple casts and literal strings, FORMAT is usually cleaner.

One practical difference: CONCAT_WS with a separator handles variable-length data (unknown number of non-NULL fields) cleanly. FORMAT is better when the output has fixed structure with a known set of placeholders.

The one thing that trips people up: || with NULL produces NULL, not a partial string.

If you're building a label from multiple columns and any might be NULL, use CONCAT or CONCAT_WS instead of ||. The || operator is the right choice when you know the inputs are not NULL, or when you want NULL to propagate intentionally — for example, when a NULL in one field means the entire record is incomplete and the label should be absent.