In PostgreSQL, I want to use an SQL statement to combine two columns and create a new column from them.
I'm thinking about using
concat(...), but is there a better way?
What's the best way to do this?
This question is related to
Generally, I agree with @kgrittn's advice. Go for it.
But to address your basic question about
concat(): The new function
concat() is useful if you need to deal with null values - and null has neither been ruled out in your question nor in the one you refer to.
If you can rule out null values, the good old (SQL standard) concatenation operator
|| is still the best choice, and @luis' answer is just fine:
SELECT col_a || col_b;
If either of your columns can be null, the result would be null in that case. You could defend with
SELECT COALESCE(col_a, '') || COALESCE(col_b, '');
But that get tedious quickly with more arguments. That's where
concat() comes in, which never returns null, not even if all arguments are null. Per documentation:
NULL arguments are ignored.
SELECT concat(col_a, col_b);
The remaining corner case for both alternatives is where all input columns are null in which case we still get an empty string
'', but one might want null instead (at least I would). One possible way:
SELECT CASE WHEN col_a IS NULL THEN col_b WHEN col_b IS NULL THEN col_a ELSE col_a || col_b END;
This gets more complex with more columns quickly. Again, use
concat() but add a check for the special condition:
SELECT CASE WHEN (col_a, col_b) IS NULL THEN NULL ELSE concat(col_a, col_b) END;
How does this work?
(col_a, col_b) is shorthand notation for a row type expression
ROW (col_a, col_b). And a row type is only null if all columns are null. Detailed explanation:
concat_ws() to add separators between elements (
ws for "with separator").
An expression like the one in Kevin's answer:
SELECT $1.zipcode || ' - ' || $1.city || ', ' || $1.state;
is tedious to prepare for null values in PostgreSQL 8.3 (without
concat()). One way (of many):
SELECT COALESCE( CASE WHEN $1.zipcode IS NULL THEN $1.city WHEN $1.city IS NULL THEN $1.zipcode ELSE $1.zipcode || ' - ' || $1.city END, '') || COALESCE(', ' || $1.state, '');
STABLE functions, not
IMMUTABLE because they can invoke datatype output functions (like
timestamptz_out) that depend on locale settings.
Explanation by Tom Lane.
This prohibits their direct use in index expressions. If you know that the result is actually immutable in your case, you can work around this with an
IMMUTABLE function wrapper. Example here: