Concat means to make one or add up something. The Concat function or method has been extensively used in the database for decades to concatenate two or more words, strings, sentences, and many more. Concatenate function does nothing when applied on the NULL arguments. Within this article, we will demonstrate the operation of the Concat function within the PostgreSQL database. Let’s start by opening up the PostgreSQL GUI application named pdAdmin 4 from the start bar of the Windows 10 desktop. While it has been opened, let’s have some examples for concatenation of strings, characters, and numbers.
Example 01
Our first example would be the Concatenation of alphabetical characters. You need to open the query editor from the taskbar of PgAdmin 4 to create a query on it. After opening the query editor, we have written the following SELECT command within the tool along with the function Concat. This Concat method takes four characters as arguments to concatenate them to a single string or word. Upon running this instruction with a “Run” button labeled on the taskbar, the output shows the Concatenated text word “Aqsa” on our pgAdmin output screen, as shown in the snap image below. This was the simplest example of concatenation for characters. Let’s move on to the next one.
SELECT concat(‘A’, ‘q’, ‘s’, ‘a’);
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/09/echo/concatenate-in-postgresql-01-300×149.png" data-lazy- height="149" src="data:image/svg xml,” width=”300″>
Example 02
This example would be using the Concat method to concatenate some words, numbers, and special characters, such as dot, @, and 14. We have given strings, numbers, and characters in a particular manner to the function “concat” that would make an email format. Hence, we have tried the following query in the editor and clicked the “Run” button to execute it. The output shows us the email result in the form of text on the output screen:
SELECT concat(‘aqsa’, ‘yasin’, 14, ‘@’, ‘gmail’, ‘.’, ‘com’);
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/09/echo/concatenate-in-postgresql-02.png" data-lazy- height="222" src="data:image/svg xml,” width=”754″>
Example 03
Let’s use the space within the Concat function as an argument for the Concatenation. This means the space will be part of the Concatenation special character. The Concat function will deal with space as a special character as it does with others. Hence, we have used two strings to be concatenated while space between them as per the query below. The result shows the output string “Aqsa Yasin” as a new string with a single space:
SELECT concat(‘Aqsa’, ‘ ‘ , ‘Yasin’);
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/09/echo/concatenate-in-postgresql-03.png" data-lazy- height="235" src="data:image/svg xml,” width=”564″>
As we have mentioned before, the Concat method doesn’t apply to any NULL argument. So, let’s have a look at it to fully understand it. Within the following query, we have used the keyword NULL between two strings Aqsa and Yasin. The applied Concat method does nothing but ignores the NULL value and creates a single string “AqsaYasin” without any space in between:
SELECT concat(‘Aqsa’, NULL, ‘Yasin’);
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/09/echo/concatenate-in-postgresql-04.png" data-lazy- height="254" src="data:image/svg xml,” width=”631″>
Example 04
The previous examples elaborate to concatenate strings, characters, and numbers with a method Concat. However, there is another way to concatenate strings or characters within databases. In this method, we see the “||” sign working to concatenate strings, characters, and integers. So, we have used the SELECT statement to concatenate two strings, “Aqsa” and “Yasin”, and one space to be concatenated via the “||” sign within it. The resultant string “Aqsa Yasin” will be displayed in the column named “Result” on the output screen:
SELECT ‘Aqsa’ || ‘ ‘ || ‘Yasin’ AS Result;
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/09/echo/concatenate-in-postgresql-05.png" data-lazy- height="254" src="data:image/svg xml,” width=”668″>
Let’s use the same style of concatenation for the emergence of a string and NULL keyword. So, we have used the following query on the editor and arrived at a NULL result. This means anything concatenated with the NULL keyword will output the NULL value as per the output below:
SELECT ‘AQSA YASIN ‘ || NULL AS Result;
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/09/echo/concatenate-in-postgresql-06.png" data-lazy- height="252" src="data:image/svg xml,” width=”681″>
Example 05
Let’s have an example of concat functions applied on several built tables of PostgreSQL. We have created a table “example” within the database Postgres of pgAmdin 4. It contains four columns having id, fname, lname, and age of a person. Then, we have inserted some records in it. The data of a table “example” can be retrieved and seen in a grid view with the query stated as follows:
SELECT * FROM public.example ORDER BY id ASC;
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/09/echo/concatenate-in-postgresql-07.png" data-lazy- height="406" src="data:image/svg xml,” width=”775″>
Now, open the query tool to add some instructions to concatenate between the values of different columns of a table “example”. So, we have used the SELECT query to retrieve the records of columns id, fname, and lname where the ID of a person is 3. We have used the Concat method on columns fname and lname to get a new value. This concatenated value will be stored in a new column, “Full Name”. This will only retrieve the records of a person having 3 as its value and concatenate its fname and lname to make the person’s full name. The query has been executed with a Run button, and the output shows the id, fname, lname, and Full Name as “Hamna Raza” of a person having its ID as 3.
SELECT id, fname, lname, concat(fname, ‘ ‘, lname) “Full Name” FROM example WHERE id=3;
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/09/echo/concatenate-in-postgresql-08.png" data-lazy- height="258" src="data:image/svg xml,” width=”745″>
Let’s concatenate more than one value from the table this time. So, we have used the same table and same query to accomplish this. The change is only on the WHERE clause. We have set the condition to fetch and concatenate the records from the table “example” where the person’s id is greater than 3. As the table has only 5 records, it will display the records of ids’ 4 and 5 along with concatenated strings shown in the display image below.
SELECT id, fname, lname, concat(fname, ‘ ‘, lname) “Full Name” FROM example WHERE id > 3;
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/09/echo/concatenate-in-postgresql-09.png" data-lazy- height="300" src="data:image/svg xml,” width=”689″>
To fetch and concatenate the records of a person having an id less than 3 from the table “example” will be done by the following query:
SELECT id, fname, lname, concat(fname, ‘ ‘, lname) “Full Name” FROM example WHERE id < 3;
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/09/echo/concatenate-in-postgresql-10.png" data-lazy- height="291" src="data:image/svg xml,” width=”781″>
Example 06
Let’s have a look at the Concat method from a different perspective. In this example, we will be using a new method, “LENGTH”, within the Concat method to get a length of one of the columns of table “example”. The calculated length will be concatenated with some other strings or sentences within the Concat method. This would be done to achieve two things. One goal is to get a length of a string, and the other is to concatenate different types of characters, numbers, and strings. The resultant concatenated whole string will be displayed in another column. So, we have used the SELECT statement to fetch column “fname” of table “example” while showing the length of each value from the column “fname” within the concatenated string as shown below:
SELECT fname, concat(‘This first name has ‘, LENGTH(fname), ‘ characters’) FROM example;
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/09/echo/concatenate-in-postgresql-11.png" data-lazy- height="400" src="data:image/svg xml,” width=”974″>
Conclusion
In this article, we discussed numerous examples to elaborate on the working of concatenation. We have used two different methods to concatenate between strings, characters, numbers, and special symbols, such as the Concat function and “||” method. We hope this article will assist you in better understanding concatenation.
About the author
<img data-del="avatar" data-lazy-src="https://kirelos.com/wp-content/uploads/2021/09/echo/Author-Image-150×150.jpg614a53f852ecf.jpg" height="112" src="data:image/svg xml,” width=”112″>
Aqsa Yasin
I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.
