Postgresql Concat + Examples

In this PostgreSQL tutorial, we will study the use of the Postgresql Concat function and we are going to discuss the following list of topics.

• Postgresql concat
• Postgresql concat two columns
• Postgresql concat string and integer
• Postgresql concat_ws
• Postgresql concat rows
• Postgresql concat rows into array
• Postgresql concat array
• Postgresql concat string with variable

Postgresql concat

In Postgresql, CONCAT is a function that is generally used to concatenate more than two strings except NULL explained in the arguments. Let’s check the syntax.

concat(<string1>,[<string2>,<string3>,...])

This function accepts a list of arguments that require to be convertible to a string. A string can be of any data type char, varchar, or text. It accepts an array as the argument. Every array element as an argument is treated by this function.

When we concatenate two or more strings into one we generally use the string concatenation operator as the below queries. In the first query, we will concatenate two strings and in another query, we will concatenate one string with NULL.

The SELECT statement used in PostgreSQL has many clauses that we are able to use to form a flexible query. Let’s check the queries.

SELECT 'UNITED' || ' ' || 'STATES' AS result_string;

SELECT 'UNITED STATES '|| NULL AS result_string;

Let’s check the output of the above query.

Also, check: Postgresql replace + Examples

Postgresql concat_ws

In Postgresql, the CONCAT function gives us the CONCAT_WS function that concatenates strings into one separated by a particular separator. The WS in concat function stands for with separator. The CONCAT_WS function is also variadic and ignores NULL values. Let’s check the below syntax.

CONCAT_WS(separator,str_1,str_2,...);

In the above syntax, the separator is a string that separates all arguments within the result string. The str_1, str_2, are strings or any arguments which are changed into strings. This function returns a combined string that is the combination of str_1, str_2, etc., detached by the separator.

Let’s take the example of table students. Now we will apply the concat_ws function to it. Let’s check the query for it.

SELECT concat_ws (', ', lastname, firstname) AS full_name
FROM students ORDER BY lastname;

Let’s check the output of the above query.

Read: PostgreSQL Subquery with Examples

Postgresql concat array

In Postgresql, this Concat function is also used to concatenate array elements using supplied delimiter and optional null string. Arrays give columns of a table to be specified as variable-length multidimensional arrays. These are created of any built-in or user-defined base type, enum type, or composite type. Let’s check an example below.

SELECT ARRAY[22,33] || ARRAY[55,66];

Let’s check the output of the above query.

Read: PostgreSQL Update Join

Postgresql concat string and integer

We can also concatenate strings with integers in Postgresql. It uses the || operator. In the below example the integer to a string is attached to the rest. Also, we need to carefully add spaces to make the string more readable. Let’s check the below example.

select 'Join these ' || 'United States with a number ' || 100;

Let’s check the output of the above query.

Read: Postgresql Add Foreign Key

Postgresql concat rows

In Postgresql, we can also concatenate two or more rows into one field. We have created a table candidate and we will implement the above query in the candidate table. Let’s check the query below in which we will concat the name and the status.

SELECT "status", STRING_AGG("name", ',') FROM "candidate" GROUP BY "status";

Let’s check the output.

Read: PostgreSQL Min With Examples

Postgresql concat rows into array

Postgresql has an aggregate function ARRAY_AGG() which acquires a set of values and returns an array in which every value in the set is allocated to an element of the array. It is a built-in function so we don’t need to define it on our own. Let’s check the below query for concatenating rows into an array.

 select n%2, array_agg(n) from generate_series(1,30) n group by n%2;

Let’s check the output of the above query.

Read: PostgreSQL add primary key

Postgresql concat two columns

In Postgresql, we can also concat two or more columns in a table. So, we have table students in which we will concat column 2 which is firstname, and column 3 which is the lastname. Let’s check the query for concatenating two columns.

select *,concat("firstname",' ', "lastname") as firstname_lastname from students;

Let’s check the output of the above query.

Read: Postgresql Having Clause

Postgresql concat string with variable

A variable is a significant name in Postgresql which is used for a memory location. It holds a value that can be changed through the function or block. A variable is always related to a particular data type. We should always declare a variable in the declaration section of the Block.

Now we will learn how to contact a string and a variable. The below example is used for concatenating strings with variables.

DO $$ 
DECLARE
   counter    INTEGER := 1;
   first_name VARCHAR(50) := 'Ross';
   last_name  VARCHAR(50) := 'Geller';
   payment    NUMERIC(11,2) := 70.5;
BEGIN 
   RAISE NOTICE '% % % has been paid % USD', counter, first_name, last_name, payment;
END $$;

Let’s check the output of the above query.

Also, take a look at some more tutorials on PostgreSQL.

• Postgresql length of string
• Postgresql Format + 28 Examples
• PostgreSQL Delete Row
• Postgresql Rank function
• PostgreSQL Like With Examples

In this tutorial, we have studied the use of the Postgresql Concat function and we have discussed the following list of topics.

• Postgresql concat
• Postgresql concat two columns
• Postgresql concat string and integer
• Postgresql concat_ws
• Postgresql concat rows
• Postgresql concat rows into array
• Postgresql concat array
• Postgresql concat string with variable