PostgreSQL allows us to concatenate a string with a number using a built-in “CONCAT()” function or a pipe operator “||”. Using these two approaches, we can add a separator, like a comma, space, etc., between a string and a number.
This blog post will show you how to concatenate a string with a number using the practical implementation of the CONCAT() function and pipe concatenation operator “||”.
Key Points
Before learning how to concatenate a string and a number in Postgres, firstly, we need to understand the following points:
- The return type of the pipe concatenation operator || and CONCAT() function is “TEXT”.
- Multiple strings can be concatenated using the CONCAT() function or the || operator.
- Using the CONCAT() function or the || operator, you can concatenate the strings with a number.
- In Postgres, you can’t concatenate only numeric values using the CONCAT() function or || operator. There must be at least one string. This is because the return type of both these approaches is TEXT.
- Concatenating a NULL value with a number or string will retrieve “NULL”.
Concatenating a String and a Number in Postgres Using CONCAT() Function
To concatenate a string with a number, users must pass a string and a number to the CONCAT() function as arguments:
CONCAT(arg_1, arg_2);
Example 1: How Do I Concatenate a String With a Number Using CONCAT() Function?
Suppose we have a string “Postgres” and the number “14.4”. Suppose we want to concatenate the given string and the number. To do so, we will execute the CONCAT() function as follows:
SELECT CONCAT('Postgres', 14.4);
The output authenticates the working of the CONCAT() function as it concatenates the given number and a string successfully.
Example 2: Concatenate a String and a Number With a Separator
You can use a separator like space, comma, semicolon, etc., between the given string and number to present the concatenated result in a better way:
SELECT CONCAT('Postgres', ':', 14.4);
The given number and string are concatenated using the ":" as a separator.
Example 3: Concatenate Table’s Column Using CONCAT() Function
In our Postgres database, we have a table named “emp_info” that contains the following records:
SELECT * FROM emp_info;
Let’s learn how to concatenate a numeric column with a string-type column using the CONCAT() function:
SELECT CONCAT(emp_id, ',', emp_name) FROM emp_info;
The output shows that a numeric column has been successfully concatenated with a text-type column.
How to Concatenate a String and a Number in Postgres Using “||” Operator?
To concatenate a string with a number, users must use the pipe concatenation operator between the input values as follows:
SELECT arg_1 || arg_2;
You can append any separator between the input values using the || operator.
Example 1: Concatenate a Number With a String Using Pipe Concatenation Operator
In this example, we will concatenate a string “PostgreSQL” and a number “14.4” using the “||” operator:
SELECT 'PostgreSQL' || ' ' || 14.4;
The output proves that the input values have been concatenated successfully.
Example 2: Concatenate a Numeric Column With a String Type Column Using || Operator
Let’s concatenate the “emp_id”, and “emp_name” columns of the “emp_info” table using the || operator:
SELECT emp_id || '-' || emp_name FROM emp_info;
The output shows that both columns have been concatenated successfully.
Conclusion
PostgreSQL allows us to concatenate a string with a number using a built-in “CONCAT()” function or a pipe operator “||”. The return type of the pipe concatenation operator || and CONCAT() function is “TEXT”. So, using these methods, you can concatenate multiple strings and numbers in Postgres. Through practical examples, this blog post explained how to concatenate a string with a number in Postgres.
