How to concatenate columns in a Postgres SELECT?
I have two string columns a and b in a table foo.
select a, b from foo returns values a and b. However, concatenation of a and b does not work. I tried :
select a || b from foo
and
select a||', '||b from foo
Update from comments: both columns are type character(2).
Answer
With string type columns like character(2) (as you mentioned later), the displayed concatenation just works because, quoting the manual:
[...] the string concatenation operator (
||) accepts non-string input, so long as at least one input is of a string type, as shown in Table 9.8. For other cases, insert an explicit coercion totext[...]
Bold emphasis mine. The 2nd example (select a||', '||b from foo) works for any data types since the untyped string literal ', ' defaults to type text making the whole expression valid in any case.
For non-string data types, you can "fix" the 1st statement by casting at least one argument to text. (Any type can be cast to text):
SELECT a::text || b AS ab FROM foo;
Judging from your own answer, "does not work" was supposed to mean "returns NULL". The result of anything concatenated to NULL is NULL. If NULL values can be involved and the result shall not be NULL, use concat_ws() to concatenate any number of values (Postgres 9.1 or later):
SELECT concat_ws(', ', a, b) AS ab FROM foo;
Or concat() if you don't need separators:
SELECT concat(a, b) AS ab FROM foo;
No need for type casts here since both functions take "any" input and work with text representations.
More details (and why COALESCE is a poor substitute) in this related answer:
Regarding update in the comment
+ is not a valid operator for string concatenation in Postgres (or standard SQL). It's a private idea of Microsoft to add this to their products.
There is hardly any good reason to use character(n)char(n)text or varchar. Details: