String Concatenation in PostgreSQL
You can concatenate the strings for different reasons. It could be that you only want to work with one string but you want to extract the values from two columns. For whatever reason, if you want to concatenate the strings, there are two options that you can use for PostgreSQL. Let’s discuss each in detail with relevant examples.
1. Using the Concatenation Operator (||)
In PostgreSQL, the (||) is a concatenation operator that you can use with strings to concatenate them. You can have more than two strings and use the same logic to join them.
For instance, if you have two strings – “Linux” and “Hint” – that you wish to concatenate, run the following command:
Note that we add an empty string as the separator. Moreover, we name our output as “name”. The first (|)| operator comes after the first string. After that, we add the empty string as the separator and the other (||) operator to concatenate the two strings. If you have multiple strings, use the same logic.
2. Using CONCAT()
With the first option of concatenation, you might notice that we created wordiness in our query. However, we can put decency to the query using CONCAT(). It offers simplicity in the concatenation and is more straightforward to use.
Its syntax is as follows:
The separator is optional, but we recommend adding it to have a neat output. It all depends on what you want to achieve with the concatenation.
To rerun the same example that we had earlier but using CONCAT(), execute the following command:
We get the same output but with a more understandable query.
If we want to give the output a more custom name, add the AS keyword followed by the custom name that we wish to use for the output.
The following example shows how we named our output as “fullname”:
Suppose you don’t add a separator. PostgreSQL merges the two strings, and you will get an output like the one in the following:
It’s possible to concatenate the strings where one of the strings is a null value. Maybe you are working with a table, and the column accepts null. In such an instance, adding the “null” keywords works fine and gets treated as an empty string.
Check the following example and see how we demonstrate such a case:
Suppose you have a table and you want to concatenate two columns. The process is the same, with a few additions. Let’s have the following table for demonstration:
Suppose we want to extract the full name of every student. We have to concatenate the “fname” with the “lname”. Our command must specify the target columns, the separator, and the table name.
Therefore, we run it as follows:
Working with CONCAT_WS()
When working with CONCAT(), we’ve seen that the separator is added between the strings. However, PostgreSQL offers the CONCAT_WS() which stands for “with separator” to allow the users to get another way to add the separator.
With this option, the separator comes first, and you can add your strings afterward. The aim is to create the order in your query. To rerun the previous query but using CONCAT_WS(), we have our command as follows:
We still get the same output, but the format for the command changes. That is how you concatenate the strings in PostgreSQL.
Conclusion
PostgreSQL string concatenation is helpful in different situations. This post described two methods to concatenate your strings quickly. Try them out and keep practicing with the method that you are more comfortable with. It’s that simple!
