With string types (including character(2)
), 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 to text
[...]
Bold emphasis mine. The 2nd example select a||', '||b from foo
works for any data types because the untyped string literal ', '
defaults to type text
making the whole expression valid.
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:
SELECT concat_ws(', ', a, b) AS ab FROM foo;
Separators are only added between non-null values, i.e. only where necessary.
Or concat()
if you don't need separators:
SELECT concat(a, b) AS ab FROM foo;
No need for type casts since both functions take "any"
input and work with text representations. However, that's also why the function volatility of both concat()
and concat_ws()
is only STABLE
, not IMMUTABLE
. If you need an immutable function (like for an index, a generated column, or for partitioning), see:
More details (and why COALESCE
is a poor substitute) in this related answer:
Asides
+
(as mentioned in comments) 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)
(synonym: char(n)
). Use text
or varchar
. Details:
text
type?concatenate
I doubt that he's dealing with numeric types, though PostgreSQL would take care of some of them as well. See here:postgresql.org/docs/9.1/static/functions-string.html