Postgres concat() function
Concatenate strings in Postgres with the concat() function
The concat()
function in Postgres is used to concatenate two or more strings into a single string. It is a variadic function, meaning it can accept any number of arguments.
It is useful for combining data from multiple columns, generating custom identifiers or labels, or constructing dynamic SQL statements.
Function signature
The concat()
function has two forms:
str
: The strings/values to concatenate. Numeric values are automatically converted to strings, whileNULL
values are treated as empty strings.
variadic str
: An array of strings/values to concatenate. This form is useful when you have an array of strings to concatenate.
Example usage
Consider a table customers
with first_name
and last_name
columns. We can use concat()
to combine these into a full name.
This query concatenates the first_name
, a space character, and the last_name
to generate the full_name
.
We can concatenate more than two strings by providing additional arguments.
This query generates a descriptive product_info
string by concatenating the name
, variant
, and price
columns along with some constant text. We used a CASE
statement to conditionally include the variant in the output.
Advanced examples
Concatenate an array of strings
You can use the variadic
form of concat()
to concatenate an array of strings.
This query concatenates the elements of the fruits
array into a single string.
Concatenate columns to generate custom keys
concat()
can be used to generate custom identifiers as keys, which you can use for further processing or analysis.
This query generates a unique identifier for each page visit by concatenating the user_id
and page
columns. We then count the number of interactions for each unique visit.
Additional considerations
Handling NULL values
Any null arguments to concat()
are treated as empty strings in the output. This is in contrast to the behavior of the ||
operator, which treats NULL
values as NULL
.
Pick the right function based on how you want to handle NULL
values.
Alternative functions
concat_ws
: Concatenates strings with a separator string between each element.string_agg
: An aggregation function that combines strings from a column into a single string with a separator.||
operator: Can also be used to concatenate strings. It treatsNULL
values differently thanconcat()
.
Resources
Last updated on