How to Properly Concatenate Columns in PostgreSQL SELECT Statements?

Joining columns in a PostgreSQL SELECT statement

When concatenating character strings in a PostgreSQL SELECT statement, you may encounter errors if the columns are not explicitly converted to text.

Question:

There are two string columns a and b in table foo. Trying to join them using a || b or a || ', ' || b returns null or unexpected results.

Solution:

To properly concatenate strings in Postgres, at least one input must be converted to text. Here are two ways to do this:

• Convert a column to text:

SELECT a::text || b AS ab FROM foo;

• Use text separator:

SELECT a || ', ' || b AS ab FROM foo;

Note:

• Using the concatenation (||) operator between non-string data types is deprecated.
• For cases where null values ​​may be involved and the result should not be null, use the concat_ws() function:

SELECT concat_ws(', ', a, b) AS ab FROM foo;

• Alternatively, you can use the concat() function for delimiter-free concatenation:

SELECT concat(a, b) AS ab FROM foo;

Additional notes:

• The
  • operator is not a valid string concatenation operator in Postgres.
• Using the character(n) data type to store strings is generally not recommended. Consider using text or varchar instead.

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