Help Center/ GaussDB/ M-Compatibility Developer Guide(Centralized)/ SQL Reference/ Type Conversion/ Explicit Type Conversion
Updated on 2025-10-23 GMT+08:00
View PDF
Share

Explicit Type Conversion

Explicit type conversion mainly applies to the scenario where type conversion functions are called. For details about type conversion functions, see Type Conversion Functions.

The explicit type conversion syntax is as follows:

SELECT CAST(expression AS data_type);

In the preceding information, expression indicates the expression to be converted and data_type indicates the data type to be converted, as shown in Table 1 and Table 2.

Table 1 Explicit cast conversion

Target Type

Data Type of Input Values (Type of Return Parameters)

SIGNED [INTEGER]

BIGINT

UNSIGNED [INTEGER]

BIGINT UNSIGNED

DATE

DATE

TIME

TIME

DATETIME

DATETIME

BINARY

TEXT

CHAR

TEXT

DECIMAL

NUMERIC

FLOAT

FLOAT4

DOUBLE

FLOAT8

JSON

JSON

Example:

-- Explicit cast usage of SIGNED
m_db=# SELECT CAST(9223372036854775808 AS SIGNED);
         cast         
----------------------
 -9223372036854775808
(1 row)

-- Boundary value processing.
m_db=# SELECT CAST(9223372036854775808.0 AS SIGNED);
WARNING:  Truncated incorrect DECIMAL value: '9223372036854775808.0'
CONTEXT:  referenced column: cast
        cast         
---------------------
 9223372036854775807
(1 row)

-- Explicit cast usage of CHAR (modifiers-limited boundaries)
m_db=# SELECT CAST(123 AS CHAR(3));
 cast 
------
 123
(1 row)
Table 2 Explicit JSON type conversion behavior

Type

CAST(expression AS JSON)

TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT, VARCHAR, CHAR, and string constants

Input text is parsed into the JSON format and the corresponding JSON type (including the NULL, BOOLEAN, INTEGER, UNSIGNED INTEGER, DOUBLE, STRING, ARRAY, and object types) is returned. If the JSON syntax is not complied with, an error is generated.

INT, TINYINT, SMALLINT, MEDIUMINT, and BIGINT

A JSON numeric scalar is converted to the INTEGER type.

INT UNSIGNED, TINYINT UNSIGNED, SMALLINT UNSIGNED, MEDIUMINT UNSIGNED, and BIGINT UNSIGNED

A JSON numeric scalar is converted to the UNSIGNED INTEGER type.

FLOAT4 and FLOAT8

These arguments can be converted to JSON numeric scalars of the DOUBLE type.

NUMERIC

A JSON numeric scalar is converted to the DECIMAL type.

BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB, BINARY, and VARBINARY

A JSON string scalar is converted to the BLOB type.

BIT

A JSON string scalar is converted to the BIT type.

YEAR

A JSON string scalar is converted to the OPAQUE type.

TIME

A JSON string scalar is converted to the TIME type.

DATE

A JSON string scalar is converted to the DATE type.

TIMESTAMP

A JSON string scalar is converted to the TIMESTAMP type.

DATETIME

A JSON string scalar is converted to the DATETIME type.

JSON

The return value is the input JSON parameter.

SET, ENUM

A JSON string scalar is converted to the STRING type.
  • You can also use the double-colon syntax for explicit type conversion, for example, SELECT 1::INT. This method is not recommended because it may cause unexpected results.
  • After the compatibility parameter m_format_dev_version is set to 's2', the explicit conversion of double colons is changed to the default implicit conversion logic, and new data types (except SET and ENUM which do not support the conversion of double colons) in M-compatible databases are supported. If the compatibility parameter is not set, the original double-colon explicit conversion logic of GaussDB is supported, and new data types in M-compatible databases are not supported.
Parent topic: Type Conversion