Type Conversion Functions
cash_words(money)
Description: Converts the money type into the text type.
Example:
1 2 3 4 5 |
gaussdb=# SELECT cash_words('1.23'); cash_words ----------------------------------- One dollar and twenty three cents (1 row) |
convert(expr, type)
Description: Converts expr into the type specified by type.
Parameter: The first parameter is an arbitrary value, and the second parameter is the type name.
Return type: the specified type
Example:
1 2 3 4 5 |
gaussdb=# SELECT convert(12.5, text); text ------ 12.5 (1 row) |
This function takes effect only in MySQL-compatible mode.
cast(x as y [DEFAULT z ON CONVERSION ERROR][,fmt])
Description: Converts x into the type specified by y. When sql_compatibility is 'MYSQL', setting b_format_version to '5.7' and b_format_dev_version to 's1' will result in x being converted into the varchar type when y is of the char type.
Parameters:
- x represents the data to be converted. Its type and value must be convertible into the target type y.
- The optional parameter DEFAULT z ON CONVERSION ERROR specifies that if x fails to be converted into the type specified by y, z will be converted into that type instead.
- The optional parameter fmt can be specified when y is any of the following types:
int1/int2/int4/int8/int16/float4/float8/numeric: The fmt parameter has the same effect as the to_number(expr [,fmt]) function.
date/timestamp/timestamp with time zone: The fmt parameter has the same effect as the to_date(string [,fmt]), to_timestamp(string [,fmt]), and to_timestamp_tz(string [,fmt]) functions.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
gaussdb=# SELECT cast('22-oct-1997' as timestamp); timestamp --------------------- 1997-10-22 00:00:00 (1 row) gaussdb=# SELECT cast('22-ocX-1997' as timestamp DEFAULT '22-oct-1997' ON CONVERSION ERROR, 'DD-Mon-YYYY'); timestamp --------------------- 1997-10-22 00:00:00 (1 row) gaussdb=# CREATE DATABASE gaussdb_m WITH dbcompatibility 'MYSQL'; gaussdb=# \c gaussdb_m -- Set compatible version control parameters. gaussdb_m=# SET b_format_version='5.7'; gaussdb_m=# SET b_format_dev_version='s1'; gaussdb_m=# SELECT cast('aaa' as char); varchar --------- aaa (1 row) |
This function supports the DEFAULT z ON CONVERSION ERROR and fmt syntaxes when a_format_version is 10c and a_format_dev_version is s1.
cast(x AS {SIGNED | UNSIGNED} [INT | INTEGER])
Description: Converts x into the BIGINT SIGNED or BIGINT UNSIGNED type.
Parameter: x represents the data to be converted. Its type and value must be convertible into the target type INT.
Return type: BIGINT SIGNED or BIGINT UNSIGNED
Example:
1 2 3 4 5 |
gaussdb=# SELECT CAST(12 AS UNSIGNED); uint8 ------- 12 (1 row) |
hextoraw(text)
Description: Converts a hexadecimal character string into the raw type.
Return type: raw
Example:
1 2 3 4 5 |
gaussdb=# SELECT hextoraw('7D'); hextoraw ---------- 7D (1 row) |
numtoday(numeric)
Description: Converts a numeric value into a timestamp in the specified format.
Return type: timestamp
Example:
1 2 3 4 5 |
gaussdb=# SELECT numtoday(2); numtoday ---------- 2 days (1 row) |
rawtohex(string)
Description: Converts a binary character string into its hexadecimal equivalent, providing the hexadecimal value corresponding to the input ASCII code.
Return type: varchar
Example:
1 2 3 4 5 |
gaussdb=# SELECT rawtohex('1234567'); rawtohex ---------------- 31323334353637 (1 row) |
to_blob(raw)
Description: Converts the RAW type into the BLOB type.
Return type: BLOB
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_blob('0AADD343CDBBD'::RAW(10)); to_blob ---------------- 00AADD343CDBBD (1 row) |
The to_blob function is supported when a_format_version is 10c and a_format_dev_version is s2.
to_bigint(varchar)
Description: Converts the character type into the bigint type.
Return type: bigint
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_bigint('123364545554455'); to_bigint ---------------- 123364545554455 (1 row) |
to_binary_double(expr)
Description: Converts expr into the float8 type.
Parameter: expr accepts numeric values of the number, float4, or float8 type, as well as character strings that can be implicitly converted into numeric values.
Return type: float8
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_binary_double('12345678'); to_binary_double ------------------ 12345678 (1 row) |
The to_binary_double function is supported when a_format_version is 10c and a_format_dev_version is s2.
to_binary_double(expr, fmt)
Description: Converts expr into the float8 type in the format specified by fmt.
Parameters: expr and fmt accept character strings of the char, nchar, varchar2, or nvarchar2 type. expr also accepts numeric values that can be implicitly converted into character strings.
Return type: float8
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_binary_double('1,2,3', '9,9,9'); to_binary_double ------------------ 123 (1 row) |
The to_binary_double function is supported when a_format_version is 10c and a_format_dev_version is s2.
to_binary_double(expr DEFAULT return_value ON CONVERSION ERROR)
Description: Converts expr into the float8 type. On failure, the default value return_value will be returned.
Parameter: expr accepts numeric values of the number, float4, or float8 type, as well as numeric values that can be implicitly converted into character strings. If expr is not a numeric or string type, an error will be reported.
Return type: float8
Example:
1 2 3 4 5 6 7 8 9 10 11 |
gaussdb=# SELECT to_binary_double(1e2 DEFAULT 12 ON CONVERSION ERROR); to_binary_double ------------------ 100 (1 row) gaussdb=# SELECT to_binary_double('aa' DEFAULT 12 ON CONVERSION ERROR); to_binary_double ------------------ 12 (1 row) |
The to_binary_double function is supported when a_format_version is 10c and a_format_dev_version is s2.
to_binary_double(expr DEFAULT return_value ON CONVERSION ERROR, fmt)
Description: Converts expr into the float8 type in the format specified by fmt. On failure, the default value return_value will be returned.
Parameters: expr and fmt accept character strings of the char, nchar, varchar2, or nvarchar2 type. expr also accepts numeric values that can be implicitly converted into character strings.
Return type: float8
Example:
1 2 3 4 5 6 7 8 9 10 11 |
gaussdb=# SELECT to_binary_double('12-' DEFAULT 10 ON CONVERSION ERROR, '99S'); to_binary_double ------------------ -12 (1 row) gaussdb=# SELECT to_binary_double('aa-' DEFAULT 12 ON CONVERSION ERROR, '99S'); to_binary_double ------------------ 12 (1 row) |
The to_binary_double function is supported when a_format_version is 10c and a_format_dev_version is s2.
to_binary_float(expr)
Description: Converts expr into the float4 type.
Parameter: expr accepts numeric values of the number, float4, or float8 type, as well as character strings that can be implicitly converted into numeric values.
Return type: float4
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_binary_float('12345678'); to_binary_float ------------------ 1.23457e+07 (1 row) |
The to_binary_float function is supported when a_format_version is 10c and a_format_dev_version is s2.
to_binary_float(expr, fmt)
Description: Converts expr into the float4 type in the format specified by fmt.
Parameters: expr and fmt accept character strings of the char, nchar, varchar2, or nvarchar2 type. expr also accepts numeric values that can be implicitly converted into character strings.
Return type: float4
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_binary_float('1,2,3', '9,9,9'); to_binary_float ------------------ 123 (1 row) |
The to_binary_float function is supported when a_format_version is 10c and a_format_dev_version is s2.
to_binary_float(expr DEFAULT return_value ON CONVERSION ERROR)
Description: Converts expr into the float4 type. On failure, the default value return_value will be returned.
Parameter: expr accepts numeric values of the number, float4, or float8 type, as well as numeric values that can be implicitly converted into character strings. If expr is not a numeric or string type, an error will be reported.
Return type: float4
Example:
1 2 3 4 5 6 7 8 9 10 11 |
gaussdb=# SELECT to_binary_float(1e2 DEFAULT 12 ON CONVERSION ERROR); to_binary_float ------------------ 100 (1 row) gaussdb=# SELECT to_binary_float('aa' DEFAULT 12 ON CONVERSION ERROR); to_binary_float ------------------ 12 (1 row) |
The to_binary_float function is supported when a_format_version is 10c and a_format_dev_version is s2.
to_binary_float(expr DEFAULT return_value ON CONVERSION ERROR, fmt)
Description: Converts expr into the float4 type in the format specified by fmt. On failure, the default value return_value will be returned.
Parameters: expr and fmt accept character strings of the char, nchar, varchar2, or nvarchar2 type. expr also accepts numeric values that can be implicitly converted into character strings.
Return type: float4
Example:
1 2 3 4 5 6 7 8 9 10 11 |
gaussdb=# SELECT to_binary_float('12-' DEFAULT 10 ON CONVERSION ERROR, '99S'); to_binary_float ------------------ -12 (1 row) gaussdb=# SELECT to_binary_float('aa-' DEFAULT 12 ON CONVERSION ERROR, '99S'); to_binary_float ------------------ 12 (1 row) |
The to_binary_float function is supported when a_format_version is 10c and a_format_dev_version is s2.
to_char(datetime/interval [, fmt])
Description: Converts a DATETIME or INTERVAL value of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE type into the TEXT type in the format specified by fmt (a template used to format date and time values).
- The optional parameter fmt accepts the following types: date, time, week, quarter, and century. Each type comes with its own distinct template. These templates can be combined as needed. Commonly used templates include HH, MI, SS, YYYY, MM, and DD. For details, see Table 1.
- fmt can include a modifier, typically "FM", to help identify leading zeros or trailing spaces.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_char(current_timestamp,'HH12:MI:SS'); to_char ---------- 10:19:26 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_char(current_timestamp,'FMHH12:FMMI:FMSS'); to_char ---------- 10:19:46 (1 row) |
to_char(double precision/real, text)
Description: Converts a floating-point value into a character string in the specified format.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_char(125.8::real, '999D99'); to_char --------- 125.80 (1 row) |
to_char(numeric/smallint/integer/bigint/double precision/real[, fmt])
Description: Converts an integer or floating-point value into a character string in the specified format.
- The optional parameter fmt accepts the following types: decimal character, digit grouping separator, plus–minus sign, and currency symbol. Each type comes with its own distinct template. These templates can be combined as needed. Commonly used templates include 9, 0, thousands separator (,), and decimal point (.). For details, see Table 1.
- A template can include a modifier like "FM", but "FM" cannot identify 0 output specified in the template.
- To convert an integer value into a hexadecimal string, you can use the template X or x.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_char(1485,'9,999'); to_char --------- 1,485 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_char( 1148.5,'9,999.999'); to_char ------------ 1,148.500 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_char(148.5,'990999.909'); to_char ------------- 0148.500 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_char(123,'XXX'); to_char --------- 7B (1 row) |
|
Format |
Description |
|---|---|
|
, (comma) |
Returns the digit grouping (thousands) separator. |
|
. (period) |
Returns the decimal point. |
|
$ |
Returns the US dollar sign ($) in the specified position. |
|
0 |
Returns leading zeros. |
|
9 |
Returns a value with the specified number of digits. |
|
B |
Returns blanks when the integer part is 0. |
|
C |
Returns the currency symbol (depending on the locale settings). |
|
D |
Returns the decimal character (depending on the locale settings). |
|
EEEE |
Returns a value in scientific notation. |
|
G |
Returns the digit grouping separator (depending on the locale settings). |
|
L |
Returns the currency symbol (depending on the locale settings). |
|
MI |
Returns the minus sign in the specified position (if the number is less than 0). |
|
PR |
Returns a negative value in <angle brackets>. |
|
RN |
Returns a Roman numeral (for any input between 1 and 3,999). |
|
S |
Returns a signed number (depending on the locale settings). |
|
TM |
Returns a value in standard or scientific notation. |
|
TM9 |
Returns a value in standard or scientific notation. |
|
TME |
Returns a value in standard or scientific notation. |
|
U |
Returns the dual currency symbol (depending on the locale settings). |
|
V |
Shifts the specified number of digits (decimal). |
|
PL |
Returns the plus sign in the specified position (if the number is greater than 0). |
|
SG |
Returns the plus or minus sign in the specified position. |
|
TH / th |
Returns the suffix for an ordinal number. |
This function supports the $, C, TM, TM9, TME, and U formats when a_format_version is 10c and a_format_dev_version is s1. However, it does not support TH, PL, or SG.
to_char(interval, text)
Description: Converts a time interval value into a character string in the specified format.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_char(interval '15h 2m 12s', 'HH24:MI:SS'); to_char ---------- 15:02:12 (1 row) |
to_char(integer, text)
Description: Converts an integer value into a character string in the specified format.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_char(125, '999'); to_char --------- 125 (1 row) |
to_char(set)
Description: Converts a value of the SET type into a character string. The SET type is currently unsupported in distributed mode.
Return type: text
to_char(numeric, text)
Description: Converts a numeric value into a character string in the specified format.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_char(-125.8, '999D99S'); to_char --------- 125.80- (1 row) |
to_char(string)
Description: Converts the CHAR, VARCHAR, VARCHAR2, or CLOB type into the TEXT type. If the value to be converted from the CLOB type exceeds the acceptable value range for the target type, an error will be reported.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_char('01110'); to_char --------- 01110 (1 row) |
to_nvarchar2(numeric)
Description: Converts the input into the nvarchar2 type.
Parameter: numeric
Return type: nvarchar2
to_char(timestamp, text)
Description: Converts a timestamp value into a character string in the specified format.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_char(current_timestamp, 'HH12:MI:SS'); to_char ---------- 10:55:59 (1 row) |
- When a_format_version is 10c and a_format_dev_version is s1, the to_char function will report an error for any incorrect fmt.
- In non-compatible mode, the to_char function will directly match FF1 for any incorrect fmt. For example, if fmt is FF10, the function will match FF1 and output 0 as it is.
to_nchar(datetime/interval [, fmt])
Description: Converts a DATETIME or INTERVAL value of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE type into the TEXT type in the format specified by fmt (a template used to format date and time values).
- The optional parameter fmt accepts the following types: date, time, week, quarter, and century. Each type comes with its own distinct template. These templates can be combined as needed. Commonly used templates include HH, MI, SS, YYYY, MM, and DD. For details, see Table 1.
- A template can include a modifier, typically "FM", to help identify leading zeros or trailing spaces.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_nchar(current_timestamp,'HH12:MI:SS'); to_nchar ---------- 10:19:26 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_nchar(current_timestamp,'FMHH12:FMMI:FMSS'); to_nchar ---------- 10:19:46 (1 row) |
to_nchar(double precision/real, text)
Description: Converts a floating-point value into a character string in the specified format.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_nchar(125.8::real, '999D99'); to_nchar --------- 125.80 (1 row) |
to_nchar(numeric/smallint/integer/bigint/double precision/real[, fmt])
Description: Converts an integer or floating-point value into a character string in the specified format.
- The optional parameter fmt accepts the following types: decimal character, digit grouping separator, plus–minus sign, and currency symbol. Each type comes with its own distinct template. These templates can be combined as needed. Commonly used templates include 9, 0, thousands separator (,), and decimal point (.). For details, see Table 1.
- A template can include a modifier like "FM", but "FM" cannot identify 0 output specified in the template.
- To convert an integer value into a hexadecimal string, you can use the template X or x.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_nchar(1485,'9,999'); to_nchar --------- 1,485 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_nchar( 1148.5,'9,999.999'); to_nchar ------------ 1,148.500 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_nchar(148.5,'990999.909'); to_nchar ------------- 0148.500 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_nchar(123,'XXX'); to_nchar --------- 7B (1 row) |
This function takes effect only in ORA-compatible mode (sql_compatibility = 'ORA') when a_format_version is 10c and a_format_dev_version is s2. In this setup, it supports the $, C, TM, TM9, TME, and U formats but does not support TH, PL, or SG.
to_nchar(interval, text)
Description: Converts a time interval value into a character string in the specified format.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_nchar(interval '15h 2m 12s', 'HH24:MI:SS'); to_nchar ---------- 15:02:12 (1 row) |
to_nchar(integer, text)
Description: Converts an integer value into a character string in the specified format.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_nchar(125, '999'); to_nchar --------- 125 (1 row) |
to_nchar(set)
Description: Converts a value of the SET type into a character string. The SET type is currently unsupported in distributed mode.
Return type: text
to_nchar(numeric, text)
Description: Converts a numeric value into a character string in the specified format.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_nchar(-125.8, '999D99S'); to_nchar --------- 125.80- (1 row) |
to_nchar(string)
Description: Converts the CHAR, VARCHAR, VARCHAR2, or CLOB type into the TEXT type. If the value to be converted from the CLOB type exceeds the acceptable value range for the target type, an error will be reported.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_nchar('01110'); to_nchar --------- 01110 (1 row) |
to_nchar(timestamp, text)
Description: Converts a timestamp value into a character string in the specified format.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_nchar(current_timestamp, 'HH12:MI:SS'); to_nchar ---------- 10:55:59 (1 row) |
to_clob(char/nchar/varchar/nvarchar/varchar2/nvarchar2/text/raw)
Description: Converts the RAW type or a text character set type (CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR, NVARCHAR2, or TEXT) into the CLOB type.
Return type: CLOB
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_clob('ABCDEF'::RAW(10)); to_clob --------- ABCDEF (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_clob('hello111'::CHAR(15)); to_clob ---------- hello111 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_clob('gauss123'::NCHAR(10)); to_clob ---------- gauss123 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_clob('gauss234'::VARCHAR(10)); to_clob ---------- gauss234 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_clob('gauss345'::VARCHAR2(10)); to_clob ---------- gauss345 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_clob('gauss456'::NVARCHAR2(10)); to_clob ---------- gauss456 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_clob('World222!'::TEXT); to_clob ----------- World222! (1 row) |
to_date(text)
Description: Converts a text value into a timestamp in the specified format.
- Format 1: Date without separators, for example, 20150814. The value must contain the complete year, month, and day.
- Format 2: Date with separators, for example, 2014-08-14. The separator can be any non-digit character.
Return type: timestamp without time zone
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_date('2015-08-14'); to_date --------------------- 2015-08-14 00:00:00 (1 row) |
When a_format_version is 10c and a_format_dev_version is s1, the value of nls_timestamp_format is YYYY-MM-DD HH24:MI:SS.
to_date(text, text)
Description: Converts a character-string value into a date in the specified format.
Return type: timestamp without time zone
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_date('05 Dec 2000', 'DD Mon YYYY'); to_date --------------------- 2000-12-05 00:00:00 (1 row) |
to_date(text [DEFAULT return_value ON CONVERSION ERROR [, fmt]])
Description: Converts text into the DATE type in the format specified by fmt. If fmt is not specified, the conversion will adhere to nls_timestamp_format instead, provided that a_format_version is 10c and a_format_dev_version is s1. However, if a_format_version and a_format_dev_version are not specified, the conversion will default to the fixed format 'yyyy-mm-dd hh24-mi-ss'.
Parameters:
- text: any expression that can be calculated as CHAR, VARCHAR2, NCHAR, NVARCHAR2, or TEXT string. If the input is null, the function will return null.
- DEFAULT return_value ON CONVERSION ERROR: This optional parameter specifies that return_value will be returned when text fails to be converted into the DATE type, provided that a_format_version is 10c and a_format_dev_version is s1. return_value can be an expression or a bound variable that must be convertible into the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or TEXT type. It can also be null. The method of converting return_value into the DATE type is the same as converting text into the DATE type. If return_value fails to be converted into the DATE type, the function will report an error.
- fmt: This optional parameter specifies the date and time format for text. If it is not specified, text must adhere to the default date format. However, if fmt is set to J, text must be an integer.
Return type: timestamp without time zone
Example:
1 2 3 4 5 6 7 8 9 10 |
gaussdb=# SELECT to_date('2015-08-14'); to_date --------------------- 2015-08-14 00:00:00 (1 row) gaussdb=# SELECT to_date('05 Dec 2000', 'DD Mon YYYY'); to_date --------------------- 2000-12-05 00:00:00 (1 row) |
gaussdb=# SET a_format_version='10c'; SET gaussdb=# SET a_format_dev_version='s1'; SET gaussdb=# SHOW nls_timestamp_format; nls_timestamp_format ---------------------------- DD-Mon-YYYY HH:MI:SS.FF AM (1 row) gaussdb=# SELECT to_date('12-jan-2022' DEFAULT '12-apr-2022' ON CONVERSION ERROR); to_date --------------------- 2022-01-12 00:00:00 (1 row) gaussdb=# SELECT to_date('12-ja-2022' DEFAULT '12-apr-2022' ON CONVERSION ERROR); to_date --------------------- 2022-04-12 00:00:00 (1 row) gaussdb=# SELECT to_date('2022-12-12' DEFAULT '2022-01-01' ON CONVERSION ERROR, 'yyyy-mm-dd'); to_date --------------------- 2022-12-12 00:00:00 (1 row)
When a_format_version is 10c and a_format_dev_version is s1, inputting a calendar year above 9999 does not necessarily trigger an error. For example, executing to_date('99999-12-12', 'yyyy-mm-dd hh24:mi:ss') will yield 9999-09-12 12:00:00. Any numbers that come after 9999 (the maximum allowed year) will be interpreted as the next fmt. This limitation also extends to the to_timestamp function.
to_number(expr [, fmt])
Description: Converts expr into the NUMBER type in the specified format. For details about type conversion formats, see Table 4. During hexadecimal-to-decimal conversion, only hexadecimal strings with a maximum of 16 bytes can be converted into unsigned numbers. It is important to note that the format value must contain only x or X characters. Otherwise, an error will be reported.
Return type: number
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_number('12,454.8-', '99G999D9S'); to_number ----------- -12454.8 (1 row) |
to_number(text, text)
Description: Converts a character-string value into a number in the specified format.
Return type: numeric
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_number('12,454.8-', '99G999D9S'); to_number ----------- -12454.8 (1 row) |
to_number(expr [DEFAULT return_value ON CONVERSION ERROR [, fmt]])
Description: Converts expr into a numeric value in the format specified by fmt. If fmt is not specified, expr must be directly convertible into a numeric value, such as '123' and '1e2'. For details about type conversion formats, see Table 5.
Parameters:
- expr: an expression that can be converted into a CHAR, VARCHAR2, NCHAR, NVARCHAR2, TEXT, INT, or FLOAT string. If the input is null, the function will return null.
- DEFAULT RETURN_VALUE ON CONVERSION ERROR: This optional parameter specifies that return_value will be returned when expr fails to be converted into the numeric type, provided that a_format_version is 10c and a_format_dev_version is s1. Similar to expr, return_value accepts any type that can be converted into a character string. Similar to expr, return_value is converted based on fmt. If the conversion fails, the function will report an error.
- fmt: This optional parameter specifies the conversion format for expr. If any input parameter is null, the function will return null.
Return type: numeric
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
gaussdb=# SET a_format_version='10c'; gaussdb=# SET a_format_dev_version='s1'; gaussdb=# SELECT to_number('1e2'); to_number ----------- 100 (1 row) gaussdb=# SELECT to_number('123.456'); to_number ----------- 123.456 (1 row) gaussdb=# SELECT to_number('123', '999'); to_number ----------- 123 (1 row) gaussdb=# SELECT to_number('123-', '999MI'); to_number ----------- -123 (1 row) gaussdb=# SELECT to_number('123' DEFAULT '456-' ON CONVERSION ERROR, '999MI'); to_number ----------- -456 (1 row) |
to_timestamp(double precision)
Description: Converts a Unix century into a timestamp.
Return type: timestamp with time zone
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_timestamp(1284352323); to_timestamp ------------------------ 2010-09-13 12:32:03+08 (1 row) |
to_timestamp(string [,fmt])
Description: Converts string into a timestamp value in the format specified by fmt. If fmt is not specified, the conversion will adhere to nls_timestamp_format instead. In GaussDB's to_timestamp:
- If the input year YYYY is 0, the function will report an error.
- If the input year YYYY is less than 0 and fmt is set to SYYYY, the function will correctly output the absolute value n of the BC year.
Characters in fmt must match data/time formats. Otherwise, an error will be reported.
Return type: timestamp without time zone
Example:
1 2 3 4 5 6 7 8 9 10 11 |
gaussdb=# SHOW nls_timestamp_format; nls_timestamp_format ---------------------------- DD-Mon-YYYY HH:MI:SS.FF AM (1 row) gaussdb=# SELECT to_timestamp('12-sep-2014'); to_timestamp --------------------- 2014-09-12 00:00:00 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_timestamp('12-Sep-10 14:10:10.123000','DD-Mon-YY HH24:MI:SS.FF'); to_timestamp ------------------------- 2010-09-12 14:10:10.123 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_timestamp('-1','SYYYY'); to_timestamp ------------------------ 0001-01-01 00:00:00 BC (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_timestamp('98','RR'); to_timestamp --------------------- 1998-01-01 00:00:00 (1 row) |
1 2 3 4 5 |
gaussdb=# SELECT to_timestamp('01','RR'); to_timestamp --------------------- 2001-01-01 00:00:00 (1 row) |
- When a_format_version is 10c and a_format_dev_version is s1, fmt supports FF[7-9]. When FF[7-9] is applied, the number of characters that can be converted from the target position in the string is limited by the number following FF, with only 6 characters ultimately being retained in the conversion result.
- The result returned by the current_timestamp function cannot be used as a string parameter.
- In PG-compatible mode, if a string exceeds the maximum length allowed by fmt, as seen in the example with to_timestamp('2024-01-05 14:52:06.694752', 'yyyy-MM-dd hh24:mi:ss'), the string will be automatically truncated to fit within the maximum length allowed by fmt, and any excess part will be discarded.
- In PG-compatible databases where the GUC parameter format_pg_to_timestamp is enabled, any unsupported formats will be skipped. The current version does not recognize formats such as FF, FF7, FF8, FF9, RR, RRRR, SYYYY, and xX. However, it supports dates prior to 294277-01-01 00:00:00 and negative years (converted into BC). Milliseconds exceeding precision will be rounded off in FF1 to FF6. When conflicting time and date formats arise, the correctly formatted one will be returned without generating an error. For details about such conflicts, see Table 2.
|
Format |
Description |
|---|---|
|
SSSSS conflicts with time. |
Retain minutes and hours of time and the seconds part of SSSSS. |
|
W and J conflict with time and date. |
Priority: Overwrite J, W, and mmdd in sequence. W: Wednesday unless otherwise specified. Default behavior: Overwrite mmdd with W. |
|
SSSSS conflicts with SS. |
Retain SS and discard SSSSS. |
|
DDD conflicts with a date. |
Discard DDD. |
to_timestamp(text [DEFAULT return_value ON CONVERSION ERROR [, fmt]])
Description: Converts text into the TIMESTAMP type in the format specified by fmt. If fmt is not specified, the conversion will adhere to nls_timestamp_format instead, provided that a_format_version is 10c and a_format_dev_version is s1. However, if a_format_version and a_format_dev_version are not specified, the conversion will default to the fixed format 'yyyy-mm-dd hh24-mi-ss'.
Parameters:
- text: any expression that can be calculated as CHAR, VARCHAR2, NCHAR, NVARCHAR2, or TEXT string. If the input is null, the function will return null.
- DEFAULT return_value ON CONVERSION ERROR: This optional parameter specifies that return_value will be returned when text fails to be converted into the TIMESTAMP type, provided that a_format_version is 10c and a_format_dev_version is s1. return_value can be an expression or a bound variable that must be convertible into the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or TEXT type. It can also be null. The method of converting return_value into the TIMESTAMP type is the same as converting text into the TIMESTAMP type. If return_value fails to be converted into the TIMESTAMP type, the function will report an error.
- fmt: This optional parameter specifies the date and time format for text. If it is not specified, text must adhere to the default date format. However, if fmt is set to J, text must be an integer.
Return type: timestamp without time zone
Example:
gaussdb=# SET a_format_version='10c'; SET gaussdb=# SET a_format_dev_version='s1'; SET gaussdb=# SELECT to_timestamp('11-Sep-11' DEFAULT '12-Sep-10 14:10:10.123000' ON CONVERSION ERROR,'DD-Mon-YY HH24:MI:SS.FF'); to_timestamp --------------------- 2011-09-11 00:00:00 (1 row) gaussdb=# SELECT to_timestamp('12-Sep-10 14:10:10.123000','DD-Mon-YY HH24:MI:SSXFF'); to_timestamp ------------------------- 2010-09-12 14:10:10.123 (1 row)
to_timestamp(text, text)
Description: Converts a character-string value into a timestamp in the specified format.
Return type: timestamp
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY'); to_timestamp --------------------- 2000-12-05 00:00:00 (1 row) |
to_timestamp_tz(string [DEFAULT return_value ON CONVERSION ERROR] [,fmt])
Description: Converts string into the timestamp with time zone type in the format specified by fmt. If fmt is not specified, the conversion will adhere to nls_timestamp_tz_format instead.
Parameters:
- DEFAULT return_value ON CONVERSION ERROR: optional parameter. It specifies that when string fails to be converted into the timestamp with time zone type, return_value will be converted into that type instead.
- fmt: optional parameter. It specifies the date and time format for string.
Return type: timestamp with time zone
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_timestamp_tz('05 DeX 2000' DEFAULT '05 Dec 2001' ON CONVERSION ERROR, 'DD Mon YYYY'); to_timestamp_tz -------------------------- 2001-12-05 00:00:00+08:00 (1 row) |
This function takes effect when a_format_version is 10c and a_format_dev_version is s1.
to_timestamp_tz(string [DEFAULT return_value ON CONVERSION ERROR], fmt, nlsparam)
Description: Converts string into the timestamp with time zone type in the format specified by fmt. If string fails to be converted into the timestamp with time zone type, return_value will be converted into that type instead. nlsparam (specifically 'nls_date_language=language') determines the language for month and day names in the specified time string. Currently, language can only be ENGLISH or AMERICAN. Using the nlsparam parameter correctly yields the same result as omitting it. For details, see Table 3.
Return type: timestamp with time zone
|
Parameter |
Type |
Description |
|---|---|---|
|
string |
text |
Character string to be converted into the timestamp with time zone type. |
|
return_value |
text |
If string fails to be converted into the timestamp with time zone type, return_value will be converted into that type instead. |
|
fmt |
text |
Date and time format for the string parameter. |
|
nlsparam |
text |
Language used for month and day names in the string parameter. |
Example:
1 2 3 4 5 |
gaussdb=# SELECT to_timestamp_tz('05 DeX 2000' DEFAULT '05 Dec 2001' ON CONVERSION ERROR, 'DD Mon YYYY','nls_date_language=AMERICAN'); to_timestamp_tz -------------------------- 2001-12-05 00:00:00+08:00 (1 row) |
This function takes effect when a_format_version is 10c and a_format_dev_version is s4.
to_dsinterval(text)
Description: Converts characters into the interval type. SQL-compatible and ISO formats are supported.
Return type: interval
Example:
1 2 3 4 5 6 7 8 9 10 11 |
gaussdb=# SELECT to_dsinterval('12 1:2:3.456'); to_dsinterval ---------------------- 12 days 01:02:03.456 (1 row) gaussdb=# SELECT to_dsinterval('P3DT4H5M6S'); to_dsinterval ----------------- 3 days 04:05:06 (1 row) |
This function takes effect when a_format_version is 10c and a_format_dev_version is s2.
to_yminterval(text)
Description: Converts characters into the interval type. SQL-compatible and ISO formats are supported.
Return type: interval
Example:
1 2 3 4 5 6 7 8 9 10 11 |
gaussdb=# SELECT to_yminterval('1-1'); to_yminterval ----------------- 1 year 1 mon (1 row) gaussdb=# SELECT to_yminterval('P13Y3M4DT4H2M5S'); to_yminterval ----------------- 13 years 3 mons (1 row) |
This function takes effect when a_format_version is 10c and a_format_dev_version is s2.
|
Format |
Description |
|---|---|
|
9 |
Returns a value with the specified number of digits. |
|
0 |
Returns leading zeros. |
|
. (period) |
Returns the decimal point. |
|
, (comma) |
Returns the digit grouping (thousands) separator. |
|
PR |
Returns a negative value in <angle brackets>. |
|
S |
Returns a signed number (depending on the locale settings). |
|
L |
Returns the currency symbol (depending on the locale settings). |
|
D |
Returns the decimal character (depending on the locale settings). |
|
G |
Returns the digit grouping separator (depending on the locale settings). |
|
MI |
Returns the minus sign in the specified position (if the number is less than 0). |
|
PL |
Returns the plus sign in the specified position (if the number is greater than 0). |
|
SG |
Returns the plus or minus sign in the specified position. |
|
RN |
Returns a Roman numeral (for any input between 1 and 3,999). |
|
TH / th |
Returns the suffix for an ordinal number. |
|
V |
Shifts the specified number of digits (decimal). |
|
x / X |
Converts a value between hexadecimal and decimal. |
|
Format |
Description |
|---|---|
|
9 |
Matches a digit. The digit "9" can occur an equal or greater number of times than the digits in the specified position in expr. |
|
0 |
Strictly matches a digit. The digit "0" must occur the same number of times as the digits in expr. |
|
5 |
Matches the digit "0" or "5". |
|
. (period) |
Returns a decimal point in the specified position. |
|
, (comma) |
Returns a digit grouping (thousands) separator in the specified position. You can specify multiple commas in fmt. |
|
B |
Returns leading blanks. |
|
PR |
Returns a negative value in <angle brackets>. Returns a positive value with a leading and trailing blank. |
|
S |
Returns a negative value with a leading minus sign (–). Returns a positive value with a leading plus sign (+). Returns a negative value with a trailing minus sign (–). Returns a positive value with a trailing plus sign (+). |
|
MI |
Returns a negative value with a trailing minus sign (–). Returns a positive value with a trailing blank. |
|
$ |
Returns a value with a leading dollar sign ($). |
|
L |
Returns the local currency symbol. |
|
C |
Returns the ISO currency symbol in the specified position. |
|
U |
Returns the dual currency symbol. |
|
D |
Returns the decimal character (depending on the locale settings). |
|
G |
Returns the digit grouping separator (complying with the ISO standard). You can specify multiple commas in fmt. |
|
RN / rn |
Returns a Roman numeral (for any input between 1 and 3,999). The to_number function does not support this format. |
|
V |
The to_number function does not support this format. |
|
X / x |
Converts a value between hexadecimal and decimal. |
|
TM |
The to_number function does not support this format. |
|
FM |
Removes any leading blanks from the beginning of fmt while retaining the placeholder 0. This does not alter the result of the to_number function. |
|
EEEE |
Returns a value in scientific notation. |
Table 5 describes the functions of fmt in cases where a_format_version is 10c and a_format_dev_version is s1, while Table 4 outlines the functions of fmt in all other cases. Of these, ISO functions are influenced by the LC_MONETARY and LC_NUMERIC parameters. Specifically, LC_MONETARY determines the currency symbol, and LC_NUMERIC affects the display rules for the decimal point and thousands separator.
cast_varchar2_to_raw_for_histogram(varchar2)
Description: Converts the varchar2 type into the raw type.
Return type: raw
abstime_text(abstime)
Description: Converts the abstime type into the text type.
Parameter: abstime
Return type: text
abstime_to_smalldatetime(abstime)
Description: Converts the abstime type into the smalldatetime type.
Parameter: abstime
Return type: smalldatetime
bigint_tid(bigint)
Description: Converts the bigint type into the tid type.
Parameter: bigint
Return type: tid
bool_int1(boolean)
Description: Converts the Boolean type into the int1 type.
Parameter: boolean
Return type: tinyint
bool_int2(boolean)
Description: Convert the Boolean type into the int2 type.
Parameter: boolean
Return type: smallint
bool_int8(boolean)
Description: Converts the Boolean type into the int8 type.
Parameter: boolean
Return type: bigint
bpchar_date(character)
Description: Converts a character string into a date.
Parameter: character
Return type: date
bpchar_float4(character)
Description: Converts a character string into the float4 type.
Parameter: character
Return type: real
bpchar_float8(character)
Description: Converts a character string into the float8 type.
Parameter: character
Return type: double precision
bpchar_int4(character)
Description: Converts a character string into the int4 type.
Parameter: character
Return type: integer
bpchar_int8(character)
Description: Converts a character string into the int8 type.
Parameter: character
Return type: bigint
bpchar_numeric(character)
Description: Converts a character string into the numeric type.
Parameter: character
Return type: numeric
bpchar_timestamp(character)
Description: Converts a character string into a timestamp.
Parameter: character
Return type: timestamp without time zone
bpchar_to_smalldatetime(character)
Description: Converts a character string into the smalldatetime type.
Parameter: character
Return type: smalldatetime
complex_array_in(cstring, oid, int2vector)
Description: Converts an external complex_array type into the internal anyarray type.
Parameters: cstring, oid, and int2vector
Return type: anyarray
date_bpchar(date)
Description: Converts the date type into the bpchar type.
Parameter: date
Return type: character
date_text(date)
Description: Converts the date type into the text type.
Parameter: date
Return type: text
date_varchar(date)
Description: Converts the date type into the varchar type.
Parameter: date
Return type: character varying
f4toi1(real)
Description: Forcibly converts the float4 type into the uint8 type.
Parameter: real
Return type: tinyint
f8toi1(double precision)
Description: Forcibly converts the float8 type into the uint8 type.
Parameter: double precision
Return type: tinyint
float4_bpchar(real)
Description: Converts the float4 type into the bpchar type.
Parameter: real
Return type: character
float4_text(real)
Description: Converts the float4 type into the text type.
Parameter: real
Return type: text
float4_varchar(real)
Description: Converts the float4 type into the varchar type.
Parameter: real
Return type: character varying
float8_bpchar(double precision)
Description: Converts the float8 type into the bpchar type.
Parameter: double precision
Return type: character
float8_interval(double precision)
Description: Converts the float8 type into the interval type.
Parameter: double precision
Return type: interval
float8_text(double precision)
Description: Converts the float8 type into the text type.
Parameter: double precision
Return type: text
float8_varchar(double precision)
Description: Converts the float8 type into the varchar type.
Parameter: double precision
Return type: character varying
i1tof4(tinyint)
Description: Converts the uint8 type into the float4 type.
Parameter: tinyint
Return type: real
i1tof8(tinyint)
Description: Converts the uint8 type into the float8 type.
Parameter: tinyint
Return type: double precision
i1toi2(tinyint)
Description: Converts the uint8 type into the int16 type.
Parameter: tinyint
Return type: smallint
i1toi4(tinyint)
Description: Converts the uint8 type into the int32 type.
Parameter: tinyint
Return type: integer
i1toi8(tinyint)
Description: Converts the uint8 type into the int64 type.
Parameter: tinyint
Return type: bigint
i2toi1(smallint)
Description: Converts the int16 type into the uint8 type.
Parameter: smallint
Return type: tinyint
i4toi1(integer)
Description: Converts the int32 type into the uint8 type.
Parameter: integer
Return type: tinyint
i8toi1(bigint)
Description: Converts the int64 type into the uint8 type.
Parameter: bigint
Return type: tinyint
int1_avg_accum(bigint[], tinyint)
Description: Adds the second parameter of the uint8 type to the first parameter. The first parameter is an array of the bigint type.
Parameters: bigint[] and tinyint
Return type: bigint[]
int1_bool(tinyint)
Description: Converts the uint8 type into the Boolean type.
Parameter: tinyint
Return type: Boolean
int1_bpchar(tinyint)
Description: Converts the uint8 type into the bpchar type.
Parameter: tinyint
Return type: character
int1_mul_cash(tinyint, money)
Description: Returns the product of a parameter of the int8 type and a parameter of the cash type. The return type is cash.
Parameters: tinyint and money
Return type: money
int1_numeric(tinyint)
Description: Converts the uint8 type into the numeric type.
Parameter: tinyint
Return type: numeric
int1_nvarchar2(tinyint)
Description: Converts the uint8 type into the nvarchar2 type.
Parameter: tinyint
Return type: nvarchar2
int1_text(tinyint)
Description: Converts the uint8 type into the text type.
Parameter: tinyint
Return type: text
int1_varchar(tinyint)
Description: Converts the uint8 type into the varchar type.
Parameter: tinyint
Return type: character varying
int1in(cstring)
Description: Converts a character string into an unsigned 1-byte integer.
Parameter: cstring
Return type: tinyint
int1out(tinyint)
Description: Converts an unsigned 1-byte integer into a character string.
Parameter: tinyint
Return type: cstring
int1up(tinyint)
Description: Converts an integer into an unsigned 1-byte integer.
Parameter: tinyint
Return type: tinyint
int2_bool(smallint)
Description: Converts a signed two-byte integer into the Boolean type.
Parameter: smallint
Return type: Boolean
int2_bpchar(smallint)
Description: Converts a signed two-byte integer into the bpchar type.
Parameter: smallint
Return type: character
int2_text(smallint)
Description: Converts a signed two-byte integer into the text type.
Parameter: smallint
Return type: text
int2_varchar(smallint)
Description: Converts a signed two-byte integer into the varchar type.
Parameter: smallint
Return type: character varying
int4_bpchar(integer)
Description: Converts a signed four-byte integer into the bpchar type.
Parameter: integer
Return type: character
int4_text(integer)
Description: Converts a signed four-byte integer into the text type.
Parameter: integer
Return type: text
int4_varchar(integer)
Description: Converts a signed four-byte integer into the varchar type.
Parameter: integer
Return type: character varying
int8_bool(bigint)
Description: Converts a signed eight-byte integer into the Boolean type.
Parameter: bigint
Return type: Boolean
int8_bpchar(bigint)
Description: Converts a signed eight-byte integer into the bpchar type.
Parameter: bigint
Return type: character
int8_text(bigint)
Description: Converts a signed eight-byte integer into the text type.
Parameter: bigint
Return type: text
int8_varchar(bigint)
Description: Converts a signed eight-byte integer into the varchar type.
Parameter: bigint
Return type: character varying
intervaltonum(interval)
Description: Converts an internal numeric date into the numeric type.
Parameter: interval
Return type: numeric
numeric_bpchar(numeric)
Description: Converts the numeric type into the bpchar type.
Parameter: numeric
Return type: character
numeric_int1(numeric)
Description: Converts the numeric type into the signed 1-byte integer type.
Parameter: numeric
Return type: tinyint
numeric_text(numeric)
Description: Converts the numeric type into the text type.
Parameter: numeric
Return type: text
numeric_varchar(numeric)
Description: Converts the numeric type into the varchar type.
Parameter: numeric
Return type: character varying
nvarchar2in(cstring, oid, integer)
Description: Converts a cstring into the varchar type.
Parameters: cstring, oid, and integer
Return type: nvarchar2
nvarchar2out(nvarchar2)
Description: Converts the text type into a cstring.
Parameter: nvarchar2
Return type: cstring
nvarchar2send(nvarchar2)
Description: Converts the varchar type into the binary format.
Parameter: nvarchar2
Return type: bytea
oidvectorin_extend(cstring)
Description: Converts a character string into the oidvector type.
Parameter: cstring
Return type: oidvector_extend
oidvectorout_extend(oidvector_extend)
Description: Converts the oidvector type into a character string.
Parameter: oidvector_extend
Return type: cstring
oidvectorsend_extend(oidvector_extend)
Description: Converts the oidvector type into a character string.
Parameter: oidvector_extend
Return type: bytea
reltime_text(reltime)
Description: Converts the reltime type into the text type.
Parameter: reltime
Return type: text
text_date(text)
Description: Converts the text type into the date type.
Parameter: text
Return type: date
text_float4(text)
Description: Converts the text type into the float4 type.
Parameter: text
Return type: real
text_float8(text)
Description: Converts the text type into the float8 type.
Parameter: text
Return type: double precision
text_int1(text)
Description: Converts the text type into the int1 type.
Parameter: text
Return type: tinyint
text_int2(text)
Description: Converts the text type into the int2 type.
Parameter: text
Return type: smallint
text_int4(text)
Description: Converts the text type into the int4 type.
Parameter: text
Return type: integer
text_int8(text)
Description: Converts the text type into the int8 type.
Parameter: text
Return type: bigint
text_numeric(text)
Description: Converts the text type into the numeric type.
Parameter: text
Return type: numeric
text_timestamp(text)
Description: Converts the text type into the timestamp type.
Parameter: text
Return type: timestamp without time zone
time_text(time without time zone)
Description: Converts the time type into the text type.
Parameter: time without time zone
Return type: text
timestamp_text(timestamp without time zone)
Description: Converts the timestamp type into the text type.
Parameter: timestamp without time zone
Return type: text
timestamp_to_smalldatetime(timestamp without time zone)
Description: Converts the timestamp type into the smalldatetime type.
Parameter: timestamp without time zone
Return type: smalldatetime
timestamp_varchar(timestamp without time zone)
Description: Converts the timestamp type into the varchar type.
Parameter: timestamp without time zone
Return type: character varying
timestamptz_to_smalldatetime(timestamp with time zone)
Description: Converts the timestamptz type into the smalldatetime type.
Parameter: timestamp with time zone
Return type: smalldatetime
timestampzone_text(timestamp with time zone)
Description: Converts the timestampzone type into the text type.
Parameter: timestamp with time zone
Return type: text
timetz_text(time with time zone)
Description: Converts the timetz type into the text type.
Parameter: time with time zone
Return type: text
to_integer(character varying)
Description: Converts the input into the integer type.
Parameter: character varying
Return type: integer
to_interval(character varying)
Description: Converts the input into the interval type.
Parameter: character varying
Return type: interval
to_numeric(character varying)
Description: Converts the input into the numeric type.
Parameter: character varying
Return type: numeric
to_text(smallint)
Description: Converts the input into the text type.
Parameter: smallint
Return type: text
to_ts(character varying)
Description: Converts the input into the ts type.
Parameter: character varying
Return type: timestamp without time zone
to_varchar2(timestamp without time zone)
Description: Converts the input into the varchar2 type.
Parameter: timestamp without time zone
Return type: character varying
varchar_date(character varying)
Description: Converts the varchar type into the date type.
Parameter: character varying
Return type: date
varchar_float4(character varying)
Description: Converts the varchar type into the float4 type.
Parameter: character varying
Return type: real
varchar_float8(character varying)
Description: Converts the varchar type into the float8 type.
Parameter: character varying
Return type: double precision
varchar_int4(character varying)
Description: Converts the varchar type into the int4 type.
Parameter: character varying
Return type: integer
varchar_int8(character varying)
Description: Converts the varchar type into the int8 type.
Parameter: character varying
Return type: bigint
varchar_numeric(character varying)
Description: Converts the varchar type into the numeric type.
Parameter: character varying
Return type: numeric
varchar_timestamp(character varying)
Description: Converts the varchar type into the timestamp type.
Parameter: character varying
Return type: timestamp without time zone
varchar2_to_smlldatetime(character varying)
Description: Converts the varchar2 type into the smlldatetime type.
Parameter: character varying
Return type: smalldatetime
xidout4(xid32)
Description: Converts the xid type into a 4-byte number.
Parameter: xid32
Return type: cstring
xidsend4(xid32)
Description: Converts the xid type into the binary format.
Parameter: xid32
Return type: bytea
bytea_blob(any)
Description: Converts the bytea type into the blob type. Currently, this conversion is limited to the bytea type.
Parameter: bytea
Return type: BLOB
treat(expr AS [JSON | REF] schema.type)
Description: Converts expr into the type (JSON or user-defined type) specified by the keyword after AS.
Return type: JSON or user-defined type
Example:
1 2 3 4 5 6 7 8 9 |
gaussdb=# CREATE TABLE json_doc(data CLOB); gaussdb=# INSERT INTO json_doc values('{"name":"a"}'); gaussdb=# SELECT treat(data as json) FROM json_doc; json -------------- {"name":"a"} (1 row) gaussdb=# DROP TABLE json_doc; DROP TABLE |
nesttable_to_array(anynesttable)
Description: Converts a nest-table collection type into an array type with the same elements.
Parameter: anynesttable
Return type: anyarray
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
gaussdb=# create or replace procedure p1 is gaussdb$# type t1 is table of int; gaussdb$# v1 t1 := t1(1, 2, 3); gaussdb$# v2 int[] := cast(v1 as int[]); gaussdb$# begin gaussdb$# raise info '%', v2; gaussdb$# end; gaussdb$# / CREATE PROCEDURE gaussdb=# call p1(); INFO: {1,2,3} p1 ---- (1 row) gaussdb=# CREATE TYPE t1 is table of int; CREATE TYPE gaussdb=# SELECT cast(t1(1, 2, 3) AS int[]) result; result --------- {1,2,3} (1 row) gaussdb=# DROP procedure p1; DROP PROCEDURE gaussdb=# DROP type t1; DROP TYPE |
indexbytableint_to_array(anyindexbytable)
Description: Converts a collection whose index type is integer into an array type with the same elements.
Parameter: anyindexbytable
Return type: anyarray
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
gaussdb=# create or replace package pkg1 is gaussdb$# type t1 is table of int index by int; gaussdb$# procedure p1(); gaussdb$# end pkg1; gaussdb$# / CREATE PACKAGE gaussdb=# gaussdb=# create or replace package body pkg1 is gaussdb$# procedure p1() is gaussdb$# v1 t1 := t1(1 => 1, 2 => 2, 3 => 3); gaussdb$# v2 int[]; gaussdb$# begin gaussdb$# v2 := cast(v1 as int[]); gaussdb$# raise info '%', v2; gaussdb$# end; gaussdb$# end pkg1; gaussdb$# / CREATE PACKAGE BODY gaussdb=# gaussdb=# call pkg1.p1(); INFO: {1,2,3} p1 ---- (1 row) gaussdb=# SELECT indexbytableint_to_array(pkg1.t1(1 => 1, 2 => 2, 3 => 3)); indexbytableint_to_array -------------------------- {1,2,3} (1 row) gaussdb=# drop package pkg1; DROP PACKAGE |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
