How to Cast an Integer to String in PostgreSQL

To convert an integer to a string in PostgreSQL, cast it to the text type with CAST(value AS text) or PostgreSQL’s value::text syntax. Use either form when a query needs a text value; use CONCAT when the goal is to combine text and values of other types. The SQL snippets below show syntax and are not accompanied by runtime output.

Cast an Integer to Text with CAST

CAST is standard SQL syntax and makes the source and destination types explicit. For example, SELECT CAST(123 AS text) AS string_value; returns a text-typed value containing 123. Replace the integer literal with a column or expression, such as CAST(order_id AS text), when your query needs that value as text. PostgreSQL calls its character string type text; “string” is the general term for the value being represented.

Use PostgreSQL’s :: Cast Operator

PostgreSQL also supports the concise :: cast operator. SELECT 123::text AS string_value; casts the same integer to the same type as the CAST form. Choose the explicit form when it reads more clearly in shared SQL, or the operator when it fits the surrounding PostgreSQL query. Both express a type conversion; neither is a formatting instruction for adding separators, padding, or other display conventions.

Compare a Text Column with an Integer in a WHERE Clause

Suppose quiz_score.score is defined as text, while the value you want to compare is an integer. Comparing score directly with 100 mixes text and integer types. Cast the integer side to text so both operands have compatible types: SELECT score FROM quiz_score WHERE score = CAST(100 AS text);. The PostgreSQL shorthand is WHERE score = 100::text.

This comparison treats the column as text. For example, the text values 100 and 0100 are distinct, even though both can represent the same number. If the intended comparison is numeric, convert the column only when its values are valid integers; otherwise the conversion can fail. For a column that is already numeric, compare it to a numeric value instead of casting it to text.

Use CONCAT to Combine Text and Numbers

CONCAT combines arguments and converts non-text arguments to text for the combined result. For example, SELECT CONCAT('The number is ', 123); builds the text The number is 123. This is useful for composing a label or message, but it is not a third spelling of a standalone cast: use CAST or ::text when the query needs the converted integer itself. In PostgreSQL, CONCAT ignores NULL arguments. To supply a replacement, use COALESCE(value::text, 'fallback') when value may be a non-text type; the cast gives both COALESCE arguments the same type.

FAQ

What is the difference between CAST and ::? CAST(value AS text) is standard SQL syntax; value::text is PostgreSQL-specific shorthand. Both cast the value to text.

Can other values be cast to text? PostgreSQL supports casts from many types, including dates and booleans. The resulting text representation depends on the source type and its output representation.

Can I use a cast in a WHERE clause? Yes. Cast the integer parameter or expression to text when comparing it with a text column, as in score = 100::text. Ensure the compared values have the same intended meaning and type.