How to Concatenate String Columns in PostgreSQL SELECT Queries?

Concatenate string columns in PostgreSQL SELECT query

In PostgreSQL, concatenating string columns directly in a SELECT query is not trivial. Consider the following example:

<code class="language-sql">select a || b from foo;</code>

Where a and b are columns of character(2) type. This will return NULL instead of the expected concatenation of the a and b values.

The simplest solution for concatenating string columns is to cast at least one column to type text:

<code class="language-sql">SELECT a::text || b AS ab FROM foo;</code>

Alternatively, you can use the concat_ws() function, which accepts any number of arguments and ignores NULL values:

<code class="language-sql">SELECT concat_ws(', ', a, b) AS ab FROM foo;</code>

If you don't need separators, you can use the concat() function:

<code class="language-sql">SELECT concat(a, b) AS ab FROM foo;</code>

concat() and concat_ws() are both volatile functions and therefore not suitable for use in indexed or generated columns. If immutability is required, you can create a custom immutable function.

Please note that the operator is not a valid string concatenation operator in Postgres. It is a unique addition by Microsoft to its products.

The above is the detailed content of How to Concatenate String Columns in PostgreSQL SELECT Queries?. For more information, please follow other related articles on the PHP Chinese website!