How to Cast an Integer to String in PostgreSQL

This tutorial discusses how to cast an integer to a string in PostgreSQL.

Cast an Integer to String in PostgreSQL

Consider a quiz_score table that keeps track of the score of each participant in a quiz game. The score is stored in this table as a string, not an integer.

Here is the CREATE statement for the table:

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

And here’s the INSERT statement to populate the table with the 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 the player or players who had a specific score and this score we are given is of type integer and not type string as stored in the table, a query like this:

SELECT * FROM quiz_score WHERE score = 72;

would 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 hint given, we would have to cast the given score, which is of type integer to string, as shown here:

SELECT * FROM quiz_score WHERE score = 72::text;

No error is thrown, and the result is given as:

Another way to cast an integer to a string is given as:

SELECT * FROM quiz_score WHERE score = cast(72 as text);

This tutorial discussed two ways to cast an integer to a string.