PostgreSQL provides us with the following ways to concatenate strings and numbers:

• The CONCAT() function, which concatenates its arguments.
• The pipe concatenation operator (||), which concatenates its operands.

Examples of each below.

The CONCAT() Function

The CONCAT() function concatenates the text representations of its arguments.

Example:

SELECT CONCAT('Player', 456);

Result:

Player456

We can include a space, either by adding a space to the existing string, or by concatenating including a third argument that consists solely of a space:

SELECT 
    CONCAT('Player ', 456) AS "Option 1",
    CONCAT('Player', ' ', 456) AS "Option 2";

Result:

  Option 1  |  Option 2  
------------+------------
 Player 456 | Player 456

The Pipe Concatenation Operator (||)

This example uses the concatenation operator (||):

SELECT 'Player ' || 456;

Result:

Player 456

In this case, I appended a space to the first argument.

Concatenating Numbers without a String

If you need to concatenate two numbers but no strings, you’ll need to cast at least one of the numbers to a string first. Otherwise an error occurs.

Example of the error:

SELECT 123 || 456;

Result:

ERROR:  operator does not exist: integer || integer
LINE 1: SELECT 123 || 456;
                   ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Here’s a quick way to overcome this issue:

SELECT CAST(123 AS varchar(3)) || 456;

Result:

123456