Convert integer to string in PostgreSQL
This tutorial discusses how to convert an integer to a string in PostgreSQL.
Convert integer to string in PostgreSQL
Consider a quiz_scoretable that records the score of each participant in a quiz game. The scores are stored in this table as strings rather than integers.
| id | player_id | score |
|---|---|---|
| 1 | 1 | 54 |
| 2 | 2 | 72 |
| 3 | 3 | 52 |
| 4 | 4 | 55 |
| 5 | 5 | 93 |
| 6 | 6 | 72 |
| 7 | 7 | 55 |
| 8 | 8 | 64 |
| 9 | 9 | 87 |
| 10 | 10 | 81 |
Here is the table CREATEstatement:
CREATE TABLE quiz_score
(
id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
player_id integer NOT NULL,
score text NOT NULL,
CONSTRAINT quiz_score_pkey PRIMARY KEY (id)
);
Here is INSERTthe statement that populates the table with data:
INSERT INTO quiz_score (player_id, score)
VALUES
(1, 54),
(2, 72),
(3, 52),
(4, 55),
(5, 93),
(6, 72),
(7, 55),
(8, 64),
(9, 87),
(10, 81);
If we are asked to find one or more players with a specific score, and we have this score as an integer rather than a string as stored in the table, a query like this:
SELECT * FROM quiz_score WHERE score = 72;
Will give the following error:
ERROR: operator does not exist: text = integer
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
Following the prompt given, we have to convert the given score (integer type) into a string as shown below:
SELECT * FROM quiz_score WHERE score = 72::text;
No errors are thrown and the results are as follows:
| id | player_id | score |
|---|---|---|
| 2 | 2 | 72 |
| 6 | 6 | 72 |
Another way to convert an integer to a string is as follows:
SELECT * FROM quiz_score WHERE score = cast(72 as text);
This tutorial discussed two methods of converting an integer to a string.
For reprinting, please send an email to 1244347461@qq.com for approval. After obtaining the author's consent, kindly include the source as a link.
Related Articles
Terminate the PostgreSQL connection
Publish Date：2025/04/11 Views：199 Category：PostgreSQL
-
In this article, we will learn how to terminate a PostgreSQL session. Any open connections are run by background processes or tasks, PSQL which may no longer exist despite exiting the user interface or command line tool. Use ps -ef or grep
Single query to rename and change column type in PostgreSQL
Publish Date：2025/04/11 Views：166 Category：PostgreSQL
-
This article describes how to rename a column and change its type in PostgreSQL using only a single query. Renaming and changing column types in MySQL In MySQL , if you want to change the column type and rename it, you can use a simple stat
Joining columns using Select in PostgreSQL
Publish Date：2025/04/11 Views：176 Category：PostgreSQL
-
MySQL PostgreSQL is an object-relational database system, which means it can support more complex data types than its competitors . Today we will learn how to use SELECT the operator to join the columns of a table. Using operators to || joi
Using CASE in PostgreSQL
Publish Date：2025/04/11 Views：124 Category：PostgreSQL
-
This article shows how to use the statement in PostgreSQL CASE . CASE How to use the statement in PostgreSQL case Statements are similar to those in general-purpose programming languages if-else . But in SQL, if you want to write IF-ELSE ,
Using NOT IN with subqueries in PostgreSQL
Publish Date：2025/04/11 Views：93 Category：PostgreSQL
-
NOT IN The inverts the result of NOT simply using IN the operator. NOT IN The right side of the operator must have a subquery in which multiple columns are returned to check whether the expression matches the data. NOT IN Tends to return tr
Using variables in PostgreSQL
Publish Date：2025/04/11 Views：171 Category：PostgreSQL
-
This article will demonstrate how we can declare and assign values to variables in PostgreSQL. In PostgreSQL, DECLARE variables are declared using Often you will need variables in your PL/SQL scripts. In DECLARE the section called , y
Connect to PostgreSQL using a password
Publish Date：2025/04/11 Views：171 Category：PostgreSQL
-
This article shows various ways to connect to PostgreSQL using a password. It can be through the command line, pgpass a file, PGPASSWORD an environment variable or a connection string. Connecting to PostgreSQL with a password using the comm
Deleting a database in PostgreSQL via PSQL
Publish Date：2025/04/11 Views：166 Category：PostgreSQL
-
There are two ways to access PostgreSQL objects and databases on your system. One is through an interface, such as a graphical interface like PGADMIN, and the other is the basic command line tool psql. Today, we will look at DROP DATABASE t
Using the database in PostgreSQL
Publish Date：2025/04/11 Views：132 Category：PostgreSQL
-
This article demonstrates connecting to a database, creating a new database, and creating a table in PostgreSQL. Available databases in PostgreSQL You can run the following command after opening the Postgres command line to view all availab
