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:
456
Although, be careful if there are leading zeros:
SELECT CONCAT(001, 456);
Result:
1456
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.