How the CONCAT() Function Works in PostgreSQL

In PostgreSQL, the CONCAT() function concatenates the text representations of its arguments.

Syntax

The syntax goes like this:

concat ( val1 "any" [, val2 "any" [, ...] ] )

Example

Here’s an example to demonstrate:

SELECT CONCAT('Squid', 'Game');

Result:

SquidGame

If we wanted a space between the strings, we can either include a space in one of the strings, or include a space as a separate argument:

SELECT 
    CONCAT('Squid ', 'Game') AS "Option 1",
    CONCAT('Squid', ' ', 'Game') AS "Option 2";

Result:

  Option 1  |  Option 2  
------------+------------
 Squid Game | Squid Game

Alternatively, we could use the CONCAT_WS() function, with a space as the first argument.

Concatenating Numbers

PostgreSQL concatenates the text representation of its arguments, so we can concatenate numbers without having to explicitly convert them to a string:

SELECT CONCAT(4, 56);

Result:

Although, be careful if there are leading zeros:

SELECT CONCAT(001, 456);

Result:

Null Arguments

Null arguments are ignored:

SELECT CONCAT('Player ', NULL, 456);

Result:

Player 456

No Arguments

Calling CONCAT() without passing any arguments results in an error:

SELECT CONCAT();

Result:

ERROR:  function concat() does not exist
LINE 1: SELECT CONCAT();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Passing an Array

It’s possible to pass an array to the CONCAT() function so that each array element becomes treated as a separate argument to be concatenated. To do this, prefix the array with the VARIADIC keyword:

SELECT CONCAT( VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ] );

Result:

CatDogHorse

We can see that each element in the array has become a separate string to be concatenated.

Using a Separator

As mentioned, PostgreSQL also has a CONCAT_WS() function that allows us to specify a separator as the first argument. This separator is placed in between each concatenated string, and can be useful if we don’t want the strings to be concatenated without any space, comma, or other separator in between them.