CONCAT, ||

Concatenate a series of strings.

CONCAT(string [, string [ , … ] ])

You can also use the || operator to concatenate a series of strings. CONCAT and || also accept values that are not character strings, such as integers and timestamps.

CONCAT and || are different in the following ways:
  • CONCAT ignores NULL values, and the || operator does not. For ||, if either argument is NULL, the result is NULL.
  • CONCAT performs implicit type casting to CHAR, and the || operator does not.
  • CONCAT has a limit of 100 strings that can be concatenated.

The following example concatenates four strings (two VARCHAR columns and two literal values).

premdb=# SELECT CONCAT(name,', The ',nickname) AS names 
FROM team LIMIT 5;
             names              
--------------------------------
 Arsenal, The Gunners
 Aston Villa, The Villainsdocs
 Birmingham City, The Blues
 Blackpool, The Seasiders
 Bolton Wanderers, The Trotters
(5 rows)

The following example concatenates three character columns:

premdb=# SELECT name||nickname||city AS names FROM team LIMIT 5;
             names              
--------------------------------
 ArsenalGunnersLondon
 Aston VillaVillainsBirmingham
 Birmingham CityBluesBirmingham
 BlackpoolSeasidersBlackpool
 Bolton WanderersTrottersBolton
(5 rows)
The following example concatenates two SMALLINT columns and a colon character:
premdb=# select concat(htid,':',atid) from team order by htid;
 concat 
--------
 2:51
 3:52
 4:53
 5:54
 6:55
 7:56
...