Convert Integer to String in PostgreSQL

1. Introduction

Converting integers to strings is a common task in SQL. It’s essential for data formatting, concatenation, and data compatibility.

In this tutorial, we’ll explore various methods for performing integer-to-string conversions in PostgreSQL.

We’ll base the examples on the Baeldung University database.

2. Problem Context and Sample Dataset

Type casting ensures compatibility between data types during operations. When we try to concatenate a string value with an integer value, we encounter “ERROR: operator does not exist: integer || text” in SQL.

Let’s examine the Faculty table, which features faculty members in departments such as Computer Science and Mathematics:

SELECT * 
FROM Faculty;

 id  |      name      | national_id |      position       | start_date |  end_date  | department_id | active
-----+----------------+-------------+---------------------+------------+------------+---------------+--------
   1 | Anubha Gupta   |  1018901231 | Professor           | 2010-01-11 | 2027-03-11 |             2 | t
 111 | AV Subramanium |  1340902317 | Assistant Professor | 2011-05-11 |            |             1 | t
 121 | Risa Sodi      |  1409239017 | Associate Professor | 2010-01-11 |            |             1 | t
 512 | Casper Jones   |  4253513301 | Teaching Assistant  | 2021-04-11 |            |             3 | t
 601 | Sussie Smith   |  1657230918 | Professor           | 2019-01-11 | 2027-02-18 |             5 | t
 740 | Kira Wass      |  2314623876 | Teaching Assistant  | 2021-09-11 |            |             4 | t
 741 | Sophia Ker     |  2314437876 | Teaching Assistant  | 2022-08-11 |            |             4 | t
(7 rows)

The table includes professors, assistant professors, and teaching assistants from various departments, totaling seven rows.

3. Explicit Typecasting

In PostgreSQL, explicit typecasting allows converting one data type to another using the :: operator:

value::target_datatype

Here, the :: operator casts a value to a new data type, for example:

national_id::text

Let’s convert national_id to text so we can concatenate it with the name for each row in the Faculty table:

SELECT CONCAT(
    SPLIT_PART(name, ' ', 1)::text,
    '_',
    national_id::text,
    '@baeldung.com'
) AS allocated_email_id 
FROM Faculty 
LIMIT 2;

allocated_email_id
---------------------------------------
[email protected]
[email protected]

In this query, we extract the first part of the name column using SPLIT_PART(name, ‘ ‘, 1). This isolates the first word from the name by using the space character as the delimiter. Then, we cast the extracted part as text to ensure it’s a string. Similarly, the national_id is explicitly cast to text. The result is an email address consisting of the first part of the name column, underscore, national_id, and the domain @baeldung.com.

Instead of ::, we can use the CAST() function:

SELECT CONCAT(
    CAST(SPLIT_PART(name, ' ', 1) AS text),
    '_', 
    CAST(national_id AS text), 
    '@baeldung.com'
) AS allocated_email_id
FROM Faculty 
LIMIT 2;

allocated_email_id
---------------------------------------
[email protected]
[email protected]

4. Implicit Typecasting

Implicit typecasting means automatically converting one data type to another when performing operations.

Consider the following query where we want to create an email address by combining a person’s name, their national_id integer, and a domain name:

SELECT CONCAT(
    SPLIT_PART(name, ' ', 1),
    '_',
    national_id,
    '@baeldung.com'
) AS allocated_email_id
FROM Faculty
LIMIT 2;

allocated_email_id
---------------------------------------
[email protected]
[email protected]

The query combines the first part of the name (before the space) with the national_id and adds @baeldung.com to create an email address for each faculty member. The national_id is an integer in the Faculty table. During the concatenation process, implicit type casting occurs: the database engine automatically converts national_id from integer to string.

5. Conclusion

In this article, we showed how to cast integer values to strings in PostgreSQL using :: or CAST(). This gives precise control over the conversion process. In contrast, implicit typecasting happens automatically when PostgreSQL safely converts compatible types. It simplifies queries by reducing the need for user intervention.