Skip to main content

Table.cast

castcolumnsvalue_typeerror_on_missing_columnson_problems

Group: Conversions

Documentation​

Cast the selected columns to a specific type. Change the value type of the selected columns with the specified type.

Arguments​

  • columns: The selection of columns to cast.
  • value_type: The Value_Type to cast the column to.
  • error_on_missing_columns: Specifies if a missing input column should result in an error regardless of the on_problems settings. Defaults to True.
  • on_problems: Specifies how to handle problems if they occur, reporting them as warnings by default.

Returns​

  • A new table with the specified columns cast to the desired type.

Errors​

  • If the target type cannot fit some of the values (for example due to too small range), a Conversion_Failure may be reported according to the on_problems rules.
  • The Database backends may fail with SQL_Error instead.

Remarks​

Casting Rules​

In the Database backend, this will boil down to a CAST operation.

In the in-memory backend, a conversion will be performed according to the following rules:

  • Anything can be cast into the Mixed type.
  • Converting to a Char type, the elements of the column will be converted to text. If it is fixed length, the texts will be trimmed or padded on the right with the space character to match the desired length.
  • Conversion between numeric types will replace values exceeding the range of the target type with Nothing.
  • Converting decimal numbers into integers will truncate or round them, depending on the backend. If more control is needed, use the various rounding functions (such as round or floor).
  • Booleans may also be converted to numbers, with True being converted to 1 and False to 0. The reverse is not supported - use iif instead.
  • A Date_Time may be converted into a Date or Time type - the resulting value will be truncated to the desired type.
  • If a Date is to be converted to Date_Time, it will be set at midnight of the default system timezone.
  • For a Mixed column being converted into a specific type, each row is converted individually.

Inexact Target Type​

If the backend does not support the requested target type, the closest supported type is chosen and a Inexact_Type_Coercion problem is reported.

Casting Text values​

The parse method should be used to convert text values into other types. Due to this, a Mixed column containing values [2, "3"] will actually be converted into [2, Nothing] when casting to Integer type.