Allowed Type Conversions

Procedural SQL (PSQL) StatementsAggregate Functions

Allowed Type Conversions

The following table shows the type conversions possible with CAST.

Table 1. Possible Type-castings with `CAST`
From	To
Numeric types	Numeric types `[VAR]CHAR` `BLOB`
`[VAR]CHAR` `BLOB`	`[VAR]CHAR` `BLOB` Numeric types `DATE` `TIME` `TIMESTAMP`
`DATE` `TIME`	`[VAR]CHAR` `BLOB` `TIMESTAMP`
`TIMESTAMP`	`[VAR]CHAR` `BLOB` `DATE` `TIME`

Keep in mind that sometimes information is lost, for instance when you cast a TIMESTAMP to a DATE.Also, the fact that types are CAST-compatible is in itself no guarantee that a conversion will succeed.“CAST(123456789 as SMALLINT)” will definitely result in an error, as will “CAST('Judgement Day' as DATE)”.

Casting Parameters

You can also cast statement parameters to a data type:

cast (? as integer)

This gives you control over the type of the parameter set up by the engine.Please notice that with statement parameters, you always need a full-syntax cast — shorthand casts are not supported.