How to Concatenate Columns in PostgreSQL SELECT Statements While Handling NULL Values?

How to join columns and handle NULL values ​​in PostgreSQL SELECT statement?

Question:

You want to concatenate two string columns (a and b) in a PostgreSQL SELECT statement. However, direct concatenation using the || or ||, ' operator returns a NULL value.

Solution:

String type joins in PostgreSQL require at least one input to be of string type. For non-string data types, an explicit conversion to a text type is required for successful concatenation.

To join columns of string type, use the following syntax:

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

Alternatively, you can use the concat_ws() function to combine values ​​using optional delimiters:

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

For non-string data types, convert to text type before concatenation, as shown in the following example:

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

Function comparison:

• concat(): Concatenate multiple values ​​without using separators.
• concat_ws(): Concatenate values ​​using an optional delimiter, adding delimiters only between non-NULL values.

The function volatility of both concat() and concat_ws() is STABLE, which means that its results may change depending on the input data. For immutable functions, consider using techniques like custom immutable cloning or full-text search.

Additional notes:

• The
  • operator is not a valid string concatenation operator in PostgreSQL or standard SQL. It only works with Microsoft products.
• It is recommended to use text or varchar to store strings instead of character(n) or char(n) data types.

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