PostgreSQL allows you to easily concatenate columns, strings and int. PostgreSQL offers two ways to concatenate strings. In this article, we will look at how to concatenate strings in PostgreSQL using each of these approaches. You can use it to concatenate columns in PostgreSQL, concatenate string and variable, or concatenate string and int.
How to Concatenate Strings in PostgreSQL
There are two ways to concatenate strings in PostgreSQL – using || operator and using concat function.
Concatenate String using || operator
PostgreSQL allows you to directly concatenate strings, columns and int values using || operator.
Let us say you have a table employees(id, first_name, last_name)
postgres=# create table employees(id int, first_name varchar(10), last_name varchar(10)); postgres=# insert into employees(id,first_name,last_name) values(1,'Jim','Doe'), (2,'Tim','Don'), (3,'Jane','Doe'); postgres=# select * from employees; id | first_name | last_name ----+------------+----------- 1 | Jim | Doe 2 | Tim | Don 3 | Jane | Doe
Also read : How to Update Multiple Columns in PostgreSQL
Here is the SQL query to concatenate columns first_name and last_name using || operator.
postgres-# select first_name || ' ' || last_name as full_name from employees; full_name ----------- Jim Doe Tim Don Jane Doe
You can even concatenate string with int using || operator.
select first_name || 123 || last_name as full_name postgres-# from employees; full_name ------------ Jim123Doe Tim123Don Jane123Doe
As you can see || operator automatically converts int to string while concatenating.
However, || operator cannot concatenate null values. If there is null in any of the column or string, then the result is a null value.
postgres-# select first_name || null || last_name as full_name from employees; full_name ----------- (3 rows)
For that purpose, we need to use concat function, which is described below.
Also read : How to Compare Arrays in PostgreSQL
Concatenate String using concat function
Concat function also allows you to concatenate strings, columns and int. It accepts comma-separated parameters. These parameters can be strings, columns, int, null, etc.
The key difference between || operator and concat function is that, if any of the values in concat function is null, it is automatically converted into an empty string during concatenation.
Here is an example to concatenate first_name and last_name column above using concat function.
postgres-# select concat(first_name,' ',last_name) as full_name from employees; full_name ----------- Jim Doe Tim Don Jane Doe
Also read : How to Change User Password in PostgreSQL
Here is an example to concatenate first_name and last_name columns with null value
postgres=# select concat(first_name,null,last_name) as full_name from employees; full_name ----------- JimDoe TimDon JaneDoe
As you can see, concat function has converted null values into empty strings.
Hopefully, this article will help you concatenate columns in PostgreSQL, concatenate string and variable, or concatenate string and int.
Need a reporting tool for PostgreSQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!