How Can I Convert VARCHAR to INT Data Type in PostgreSQL?

How to Convert VARCHAR to INT Data Type in PostgreSQL

In PostgreSQL, the CAST or :: operator can be used to cast a data type to another. This is useful when you need to convert a VARCHAR column to an INT data type for mathematical operations or filtering.

CAST Operator:

<code class="sql">SELECT CAST(varchar_col AS int) FROM table_name;</code>

:: Operator (Shorthand):

<code class="sql">SELECT varchar_col::int FROM table_name;</code>

Both syntax variants can be used in most places where data types need to be converted. However, the :: operator may require parentheses in certain situations, such as when the conversion is part of a nested function or statement.

Other valid but less common casting syntax includes:

<code class="sql">int4(varchar_col)
int '123'</code>

Note that int4 is the internal name for the INT4 data type in PostgreSQL. The last form (int '123') only works for untyped, quoted string literals.

Rules for Integer Conversion:

The string being converted to an integer must follow specific rules:

• Leading/trailing spaces are ignored.
• An optional /- sign can precede the digits.
• The string must consist of digits only.

Example:

Consider a table with a VARCHAR column called "age". The following query converts the values in the "age" column to integers:

<code class="sql">SELECT CAST(age AS int) FROM table_name;</code>

This query returns a table with an INT data type column, containing the converted ages from the original VARCHAR column.

The above is the detailed content of How Can I Convert VARCHAR to INT Data Type in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!