Scalar Data Conversion Functions
These functions convert one data type to another.
Function | Syntax | Purpose |
|---|---|---|
BYTE | BYTE(numeric value) | Casts the argument to a value of type byte |
| BYTE(character value) | Parses the string as a byte value |
SMALLINT | SMALLINT(numeric value) | Casts the argument to a value of type smallint |
| SMALLINT(character value) | Parses the string as a smallint value |
INTEGER | INT(numeric value) or INTEGER(numeric value) | Casts the argument to a value of type integer |
| INT(character value) or INTEGER(character value) | Parses the string as an integer value |
BIGINT | BIGINT(numeric value) | Casts the argument to a value of type bigint |
| BIGINT(character value) | Parses the string as a bigint value |
| BIGINT(timestamp value) | Creates a bigint with the number of milliseconds since the Unix® epoch corresponding to the timestamp argument |
FLOAT | FLOAT(numeric value) | Casts the argument to a value of type float |
| FLOAT(character value) | Parses the string as a float value |
DOUBLE | DOUBLE(numeric value) | Casts the argument to a value of type double |
| DOUBLE(character value) | Parses the string as a double value |
DECIMAL | DECIMAL(numeric value) | Casts the argument to a value of type decimal |
| DECIMAL(character value) | Parses the string and creates a decimal value |
| DECIMAL(numeric value, integer value, integer value) | Casts the first argument to a decimal value with precision of the second argument and scale of the third argument |
| DECIMAL(character value, integer value, integer value) | Parses the first argument to a decimal value with precision of the second argument and scae of the third argument |
CHAR | STR | CHAR(numeric value) | Creates a string version of the numeric value |
| CHAR(character value) | no-op |
| CHAR(timestamp) | Creates a string representation of the timestamp |
| CHAR(date) | Creates a string representation of the date |
| CHAR(time) | Creates a string representation of the time |
| CHAR(boolean) | Creates a string representation of the Boolean value |
| CHAR(binary) | Creates a hexadecimal string representation of the binary value |
| CHAR(ip) | Creates a string representation of the IPV4 or IPV6 value |
| CHAR(ipv4) | Creates a string representation of the IPV4 value |
| CHAR(hash) | Creates a string representation of the fixed-binary hash value |
| CHAR(point) | Creates a string representation of the point value |
| CHAR(uuid) | Creates a string representation of the uuid |
BINARY | BINARY(character value) | Parses a hexadecimal string (such as 0x54ab) to create a binary value. Letters can be of either case. |
| BINARY(binary) | no-op |
| BINARY(hash) | Converts the fixed length binary value (hash) to a variable length binary value |
BOOLEAN | BOOLEAN(character value) | Parses a string to create a Boolean value. The string must contain either true or false. It is case-insensitive. |
| BOOLEAN(boolean) | no-op |
DATE | DATE(character value) | Parses a string in the form 'YYYY-MM-DD' to create a date. Extra characters are ignored. |
| DATE(date) | no-op |
| DATE(timestamp) | Truncates a timestamp to be a date value |
HASH | HASH(char, length) | Creates a fixed length binary value, with length <length>, from a string, i.e. 0x1234abcd. Zero extended if string does not have enough bytes, truncated if it has too many. |
| HASH(binary, length) | Creates a fixed length binary value, with length <length>, from a variable length binary value. Zero extended if string does not have enough bytes, truncated if it has too many. |
IP | IP(character value) | Creates an value of type IPV4 or IPV6 from a string |
IPV4 | IPV4(character value) | Parses a string containing an IP address and creates a value of type IPV4 |
| IPV4(ipv4) | no-op |
TIME | TIME(character value) | Parses the string to create a time value. The string must be in the form 'HH:MM[.SSSSSSSSS]' |
| TIME(bigint) | Creates a time value representing a certain number of milliseconds since midnight |
| TIME(time) | no-op |
| TIME(timestamp) | Makes a time value from the time component of a timestamp |
TIMESTAMP | TIMESTAMP(character value) | Parses a string and makes a timestamp value. The string must be in the format 'YYYY-MM-DD[ HH:MM][.SSSSSSSSS]'. |
| TIMESTAMP(date) | Makes a timestamp from the date by setting the time component to 00:00:00.0 |
| TIMESTAMP(timestamp) | no-op |
| TIMESTAMP(bigint value) | Creates a timestamp from a bigint representing the number of milliseconds since the Unix epoch (1/1/1970). |
UUID | UUID(character value) | Parses the string and makes a UUID value. The string must be a valid UUID. |
| UUID(uuid) | no-op |
WEEKS | WEEKS(integral value) | Converts an integral value to an interval value of type weeks to be used in date calculations. |
| Weeks(weeks) | no-op |
DAYS | DAYS(integral value) | Converts an integral value to an interval value of type days to be used in date calculations. |
| DAYS(days) | no-op |
| DAYS(weeks) | Converts an interval value of type weeks to an interval value of type days representing the same time span. |
MONTHS | MONTHS(integral value) | Converts an integral value to an interval value of type months to be used in date calculations. |
| MONTHS(months) | no-op |
| MONTHS(years) | Converts an interval value of type years to an interval value of type months representing the same time span |
YEARS | YEARS(integral value) | Converts an integral value to an interval value of type years |
| YEARS(years) | no-op |
HOURS | HOURS(integral value) | Converts an integral value to an interval value of type hours |
| HOURS(hours) | no-op |
| HOURS(days) | Converts an interval value of type days to an interval value of type hours representing the same time span. |
| HOURS(weeks) | Converts an interval value of type weeks to an interval value of type hours representing the same time span. |
MINUTES | MINUTES(integral value) | Converts an integral value to an interval value of type minutes |
| MINUTES(minutes) | no-op |
| MINUTES(hours) | Converts an interval value of type hours to an interval value of type minutes representing the same time span |
| MINUTES(days) | Converts an interval value of type days to an interval value of type minutes representing the same time span |
| MINUTES(weeks) | Converts an interval value of type weeks to an interval value of type minutes representing the same time span |
SECONDS | SECONDS(integral value) | Converts an integral value to an interval value of type seconds |
| SECONDS(seconds) | no-op |
| SECONDS(minutes) | Converts an interval value of type minutes to an interval value of type seconds representing the same time span |
| SECONDS(hours) | Converts an interval value of type hours to an interval value of type seconds representing the same time span |
| SECONDS(days) | Converts an interval value of type days to an interval value of type seconds representing the same time span |
| SECONDS(weeks) | Converts an interval value of type weeks to an interval value of type seconds representing the same time span |
MILLISECONDS | MILLISECONDS(integral value) | Converts an integral value to an interval value of type milliseconds |
| MILLISECONDS(milliseconds) | no-op |
| MILLISECONDS(seconds) | Converts an interval value of type seconds to an interval value of type milliseconds representing the same time span |
| MILLISECONDS(minutes) | Converts an interval value of type minutes to an interval value of type milliseconds representing the same time span |
| MILLISECONDS(hours) | Converts an interval value of type hours to an interval value of type milliseconds representing the same time span |
| MILLISECONDS(days) | Converts an interval value of type days to an interval value of type milliseconds representing the same time span |
| MILLISECONDS(weeks) | Converts an interval value of type weeks to an interval value of type milliseconds representing the same time span |
MICROSECONDS | MICROSECONDS(integral value) | Converts an integral value to an interval value of type microseconds |
| MICROSECONDS(milliseconds) | Converts an interval value of type milliseconds to an interval value of type microseconds representing the same time span |
| MICROSECONDS(seconds) | Converts an interval value of type seconds to an interval value of type microseconds representing the same time span |
| MICROSECONDS(minutes) | Converts an interval value of type minutes to an interval value of type microseconds representing the same time span |
| MICROSECONDS(hours) | Converts an interval value of type hours to an interval value of type microseconds representing the same time span |
| MICROSECONDS(days) | Converts an interval value of type days to an interval value of type microseconds representing the same time span |
| MICROSECONDS(weeks) | Converts an interval value of type weeks to an interval value of type microseconds representing the same time span |
| MICROSECONDS(microseconds) | no-op |
NANOSECONDS | NANOSECONDS(integral value) | Converts an integral value to an interval value of type nanoseconds |
| NANOSECONDS(microseconds) | Converts an interval value of type microseconds to an interval value of type nanoseconds representing the same time span |
| NANOSECONDS(milliseconds) | Converts an interval value of type milliseconds to an interval value of type nanoseconds representing the same time span |
| NANOSECONDS(seconds) | Converts an interval value of type seconds to an interval value of type nanoseconds representing the same time span |
| NANOSECONDS(minutes) | Converts an interval value of type minutes to an interval value of type nanoseconds representing the same time span |
| NANOSECONDS(hours) | Converts an interval value of type hours to an interval value of type nanoseconds representing the same time span |
| NANOSECONDS(days) | Converts an interval value of type days to an interval value of type nanoseconds representing the same time span |
| NANOSECONDS(weeks) | Converts an interval value of type weeks to an interval value of type nanoseconds representing the same time span |
| NANOSECONDS(nanoseconds) | no-op |
String literals can be cast to another data type by placing the name of the data before the string literal, such as double '1.5'.
Interval literals can also be constructed using standard SQL syntax, for example interval '5 days'.
You can perform explicit casts of some expression expr to another data type type with either CAST(expr AS type) or expr::type[].