PostgreSQL Concatenate Function for Joining Strings
Introduction
When you’re querying data in PostgreSQL, you want your results to be returned in a format that’s easy to read and understand. Joining, or concatenating, strings can help transform the results of a query, making them clear and readable. The PostgreSQL CONCAT()
function makes it easy to accomplish this task. In this article, we’ll show you how to use the PostgreSQL concatenate function to join one or more strings into one output string.
Prerequisites
A few key prerequisites need to be in place for this tutorial:
- You’ll need to have PostgreSQL installed on your machine.
- You’ll need to have access to the PostgreSQL command-line interface.
- You should have a basic understanding of database management systems, especially PostgreSQL.
Open command-line interface
In order to execute queries that make use of the PostgreSQL concatenate function, we’ll need to access the Postgres command-line interface.
To start the process, use the command shown below to elevate your privileges to those of the superuser:
1 | sudo su - postgres |
Then, use the psql
command to enter the interface:
1 | psql |
PostgreSQL Concatenate
The PostgreSQL concatenate function CONCAT()
allows you to join two or more strings into one output string.
Create Database in PostgreSQL
Let’s start by creating a sample database to use in our examples. We’ll use the following SQL statement:
1 | CREATE DATABASE test_db; |
To connect to the new database, enter \c
followed by the name of the database you just created.
Create Table in PostgreSQL
Our next step is to create a table in this new database. The following syntax is used to create a table in PostgreSQL:
1 2 | CREATE TABLE TABLE_NAME (column_name + DATA TYPE + CONSTRAINT [optional]); |
Note: The constraint is optional. It can be used to define rules or to limit the type of data in the table.
Insert records in PostgreSQL table
Now that we’ve created a new table in our database, let’s add some records to it. To insert data, the following syntax is used:
1 2 | INSERT INTO TABLE_NAME (COLUMN_1,COLUMN_2...,) VALUES (VALUES_1,VALUES_2...,) |
In PostgreSQL, you can insert multiple values into a record by using commas to separate the list of columns in the database table. We can see how this works in the INSERT
statement below:
1 2 3 4 5 6 7 | INSERT INTO users(id, first_name, last_name, age, address) VALUES (1, 'justine', 'bibe', 20, 'america'), (2, 'kimmy', 'david', 29, 'spain'), (3, 'josh', 'bieber', 25, 'korea'); INSERT 0 3 |
To verify that this insert operation was successful, we can perform a simple SELECT
statement:
1 2 3 4 5 6 7 | SELECT * FROM users; id | first_name | last_name | age | address ----+------------+-----------+-----+--------- 1 | justine | bibe | 20 | america 2 | kimmy | david | 29 | spain 3 | josh | bieber | 25 | korea (3 ROWS) |
The users table shown above is the sample table we’ll use to demonstrate the CONCAT()
function in PostgreSQL.
PostgreSQL concatenate examples
In our example, we’ll show how to concatenate two strings into one using the _firstname and _lastname columns in the users table.
Let’s look at this example:
1 2 3 4 | SELECT CONCAT (first_name, ' ', last_name) AS "complete name" FROM users; |
Notice that we added a space between the first_name
and last_name
columns.
Our result will look like this:
1 2 3 4 5 6 | complete name --------------- justine bibe kimmy david josh bieber (3 ROWS) |
PostgreSQL concatenate using comma separator example
If you’d like to use a comma as a separator between two strings, use the syntax shown in this example:
1 2 3 4 | SELECT CONCAT_WS(', ',first_name, last_name) AS complete_name FROM users ORDER BY first_name; |
In this case, the result will look like the following:
1 2 3 4 5 6 | complete_name --------------- josh, bieber justine, bibe kimmy, david (3 ROWS) |
The CONCAT_WS()
function call used in this example allows you to pass a special separator or delimiter, such as a comma, when concatenating strings. The “WS” in the function name stands for “with separator”.
PostgreSQL concatenate NULL values example
The next example shows an attempt to concatenate NULL
values:
1 2 | SELECT CONCAT ('object', NULL) AS result_string; |
The PostgreSQL concatenate function simply ignores the NULL
arguments.
PostgreSQL concatenate using LENGTH function
The LENGTH
function in PostgreSQL returns the length of the specified string. You can incorporate the value returned by the LENGTH
function in a concatenation of strings:
1 2 3 4 | SELECT last_name, concat ('your name is composed of ', LENGTH (last_name),' characters') FROM users; |
The CONCAT
function will return the following result:
1 2 3 4 5 6 | last_name | concat -----------+--------------------------------------- bibe | your name IS composed OF 4 characters david | your name IS composed OF 5 characters bieber | your name IS composed OF 6 characters (3 ROWS) |
Conclusion
Joining two or more strings into one can help make your PostgreSQL query results easier to read, helping you gain better insights from your data. In this article, we showed you how to use the PostgreSQL concatenate function to join multiple strings into a single output string and showed you examples to illustrate how this function can be used. If you’ve followed along with our examples, you’ll be ready to implement the CONCAT()
function in your own PostgreSQL queries.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started