PostgreSQL - concatenate multiple columns into one

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:

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:

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:

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');