Languages
[Edit]
EN

PostgreSQL - concatenate multiple columns into one

0 points
Created by:
leila
435

In this article, we would like to show you how to concatenate multiple columns into one in PostgreSQL.

Quick solution:

SELECT CONCAT("column1", 'separator', "column2", 'separator', "columnN") AS 'alias_name'
FROM "table_name";
SELECT CONCAT_WS('separator', "column1", "column2", "columnN") AS 'alias_name'
FROM "table_name";

Practical example

To show how to combine multiple columns into one, we will use the following table:

PostgreSQL - example data used to concatenate columns
PostgreSQL - example data used to concatenate columns

Note:

At the end of this article you can find database preparation SQL queries.

Example 1 - using CONCAT()

In this example, we will concatenate name and surname columns into one - full_name using empty string (' ') as a separator. Then we will combine the rest of the columns into the second one - address using commas (', ') as separators.

Note:

Go to the next example to see how to specify the separator (such as ', ') only once when you want to concatenate many columns.

Query:

SELECT 
    CONCAT("name", ' ', "surname") AS 'full_name',
    CONCAT("country", ', ', "city", ', ', "street") AS 'address'
FROM "users";

Output:

PostgreSQL - concatenate multiple columns into one - result
PostgreSQL - concatenate multiple columns into one - result

Example 2 - using CONCAT_WS()

In this example, we will concatenate country, city and street columns into one - address, using CONCAT_WS() function.

Query:

SELECT
    "name", "surname", 
    CONCAT_WS(', ', "country", "city", "street") AS 'address'
FROM "users";

Output:

PostgreSQL - concatenate multiple columns into one using CONCAT_WS - result
PostgreSQL - concatenate multiple columns into one using CONCAT_WS - result

Database preparation

create_tables.sql file:

CREATE TABLE "users" (
	"id" SERIAL,
	"name" VARCHAR(50) NOT NULL,
	"surname" VARCHAR(50) NOT NULL,
	"country" VARCHAR(100) NOT NULL,
	"city" VARCHAR(100) NOT NULL,
	"street" VARCHAR(100) NOT NULL,
	PRIMARY KEY ("id")
);

insert_data.sql file:

INSERT INTO "users"
	( "name", "surname", "country", "city", "street")
VALUES
	('Tomasz', 'Kowalski', 'Poland', 'Warsaw', 'Mickiewicza Street 1'),
	('Chris', 'Brown', 'United States', 'New York', 'Main Street 12'),
	('Minh', 'Nguyen', 'Vietnam', 'Hanoi', 'Dong Xuan Street 123');
Donate to Dirask
Our content is created by volunteers - like Wikipedia. If you think, the things we do are good, donate us. Thanks!
Join to our subscribers to be up to date with content, news and offers.

PostgreSQL - problems

PostgreSQL - concatenate multiple columns into one
Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join