How to Cast Data Types within SELECT Statements in PostgreSQL?

Casting Data Types in Postgres SELECT Statements

In Postgres 8, it is possible to convert a column's data type to another within a SELECT statement.

Convert Varchar to Int

To cast a varchar column to an int, use the following syntax:

<code class="sql">cast(varchar_col AS int)  -- SQL standard
varchar_col::int          -- Postgres syntax shorthand</code>

These options are nearly universally applicable. The latter form may require additional nested parentheses in certain situations, while the former may be necessary in functional notation contexts.

Example

<code class="sql">SELECT cast(age_str AS int) AS age_int
FROM customer_info;</code>

Additional Notes

• PostgreSQL allows four other casting variants:

  • int4(varchar_col) (works for specific type names)
  • int '123' (untyped quoted string literals)

• The string value must follow specific formatting:

  • Optional leading sign ( /-)
  • Digits only
  • Leading/trailing whitespaces are ignored
• See the Postgres manual for more details on casting.

The above is the detailed content of How to Cast Data Types within SELECT Statements in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!