How Do I Cast Data Types in PostgreSQL?

PostgreSQL data type conversion

In PostgreSQL, you may need to convert data from one type to another in a SELECT statement. For example, you might have a column that contains data of type VARCHAR and need to convert it to type INTEGER.

Grammar

To convert data types in Postgres, you can use one of the following syntaxes:

• cast(varchar_col AS int) (SQL standard)
• varchar_col::int (Postgres abbreviation)

These syntaxes can be used almost anywhere. In some cases, you may need to nest parentheses within the cast function, such as when creating an index on a field length.

Other variations

Two less common variations of the cast syntax:

• int4(varchar_col) (only works with certain type names)
• int '123' (must be an untyped, quoted string literal)

Note that the int4 variant uses an internal type name. The integer() and int() functions do not work with conversions.

Restrictions and Notes

• Strings converted to integers must follow the format of an integer (optional leading symbol followed by a number).
• Leading or trailing spaces in the string will be ignored.
• Converting to an array type requires using '{}' around the value, followed by ::int[] conversion.
• For more detailed information, please refer to the PostgreSQL manual.

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