To concatenate (combine) two columns into a single column in PostgreSQL using the || operator or the concat() function.
- Concatenate two character columns in PostgreSQL using concat() function and || Operator
- Concatenate character and numeric / integer column in PostgreSQL
Using || Operator to concatenate two columns in PostgreSQL
- The || operator in PostgreSQL is used for string concatenation. You can use it to concatenate two columns together in a SELECT
We will be using Employeetbl table.
In the above table we have first_name and last_name, and we want to concatenate these two columns to create a new column full_name this can be accomplished by simple || operator.
SELECT *, first_name || ' ' || last_name AS full_name FROM employeetbl;
Output:
Using CONCAT() function to concatenate two columns in PostgreSQL
Example 1:
- The CONCAT()function in PostgreSQL is also used for string concatenation. You can use it to concatenate two columns together in a SELECT concat() function, which takes multiple arguments and concatenates them into a single string.
SELECT *, concat(first_name, ' ', last_name) AS full_name FROM employeetbl;
concat(first_name, ‘ ‘, last_name) achieves the same concatenation of first_name, space ‘ ‘, and last_name
Output:
Example 2:
CONCAT() function to concatenate with hypen in between first_name and last_name
SELECT *, concat(first_name, '-', last_name) AS full_name FROM employeetbl;
concat(first_name, ‘-‘, last_name) achieves the same concatenation of first_name, hypen (-) and last_name
Output:
Concatenate character and numeric / integer column in PostgreSQL using || operator:
To Concatenate the character and numeric column in PostgreSQL first we need to typecast the numeric column to character column and then concatenate them using || operator
SELECT *, first_name || ' ' || last_name||' '||eno::text AS full_name_eid FROM employeetbl;
“eno” column is integer column so we will be typecasting them to character column before concatenating as shown above
Output:
Concatenate character and numeric / integer column in PostgreSQL using CONCAT():
To Concatenate the character and numeric column in PostgreSQL first we need to typecast the numeric column to character column and then concatenate them using concat() function
SELECT *, concat(first_name, ' ', last_name,' ',eno::text) AS full_name_eid FROM employeetbl;
“eno” column is integer column so we will be typecasting them to character column before concatenating as shown above
Output:
Conclusion Note:
- Ensure that the data types of the columns being concatenated are compatible with string concatenation (|| operator or concat() function).
- If any of the columns can be NULL, you may need to handle that scenario using COALESCE() or CONCAT_WS() (which handles NULLs better).
