The following mathematical functions are supported:
CONV
LOG10
LOG2
RAND
TRUNCATE
Description
Convert numbers between different number bases.
Syntax
conv( p_num text, p_from_base int4, p_to_base int4) RETURNS text
General rules
Converts numbers between different number bases.
Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string.
The minimum base is 2 and the maximum base is 36. If from_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV() works with 64-bit precision.
Example
In the following example, '123456' converted from base 10 to base 16 is 1E240.
select conv('123456', 10, 16);
conv
-------
1E240
(1 row)
Description
return the base-10 logarithm of the argument.
Syntax
log10(p1 numeric) RETURNS numeric
log10(p1 double precision) RETURNS double precision
General rules
Returns the base-10 logarithm of p1. If X is less than or equal to 0.0E0, the function returns NULL and a warning “Invalid argument for logarithm” is reported.
Example
In the following example, return the base-10 logarithm of the argument 2.
select log10(2);
log10
--------------------
0.3010299956639812
(1 row)
Description
Return the base-2 logarithm of the argument.
Syntax
log2(p_num numeric) RETURNS numeric
General rules
Returns the base-2 logarithm of p_num. If p_num is less than or equal to 0.0E0, the function returns NULL and a warning “Invalid argument for logarithm” is reported.
Example
In the following example, return the base-2 logarithm of the argument 64.
select log2(64);
log2
--------------------
6.0000000000000000
(1 row)
Description
Returns a random floating-point value v in the range 0 and 1.0.
Syntax
rand(p_seed int default null) RETURNS double precision
General rules
With a constant initializer argument, the seed is initialized once when the statement is prepared, prior to execution.
With a nonconstant initializer argument (such as a column name), the seed is initialized with the value for each invocation of RAND().
One implication of this behavior is that for equal argument values, RAND(N) returns the same value each time, and thus produces a repeatable sequence of column values.
RAND() in a WHERE clause is evaluated for every row (when selecting from one table) or combination of rows (when selecting from a multiple-table join).
Example
In the following example, the sequence of values produced by RAND(123) is the same both places it occurs.
select rand(123);
rand
---------------------
0.05425240159967615
(1 row)
Description
Truncate to specified number of decimal places.
Syntax
TRUNCATE(SMALLINT X,INTEGER D) returns INTEGER
TRUNCATE(INTEGER X,INTEGER D) returns INTEGER
TRUNCATE(BIGINT X,INTEGER D) returns BIGINT
TRUNCATE(DOUBLE PRECISION X,INTEGER D) returns DOUBLE PRECISION
TRUNCATE(NUMERIC,INTEGER D) returns NUMERIC
General rules
Returns the number X, truncated to D decimal places.
If D is 0, the result has no decimal point or fractional part.
D can be negative to cause D digits left of the decimal point of the value X to become zero.
Example
In the following example, the result of "1.223" truncated to 1 decimal places is returned.
SELECT TRUNCATE(1.223,1);
truncate
----------
1.2
(1 row)
The following string functions are supported:
ELT
FIELD
FIND_IN_SET
INSTR
FROM_BASE64
INSERT
LCASE
LOCATE
MID
SPACE
STRCMP
TO_BASE64
UCASE
SUBSTR
SUBSTRING
SUBSTRING_INDEX
Description
Return string at index number.
Syntax
elt(str_pos int, VARIADIC strlist text[]) RETURNS text
General rules
ELT() returns the str_posth element of the list of strings: str1 if str_pos = 1, str2 if str_pos = 2, and so on.
Returns NULL if N is less than 1 or greater than the number of arguments.
ELT() is the complement of FIELD().
Example
In the following example,Return 'Bb' at 2.
select elt( 2, 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
elt
-----
Bb
(1 row)
Description
Index (position) of first argument in subsequent arguments.
Syntax
field(str text, VARIADIC strlist text[]) RETURNS bigint
field(str numeric, VARIADIC strlist numeric[]) RETURNS bigint
General rules
Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.
If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.
If str is NULL, the return value is 0 because NULL fails equality comparison with any value. FIELD() is the complement of ELT().
Example
In the following example,Index (position) of 'Bb' in 'Aa', 'Bb', 'Cc', 'Dd', 'Ff' 2 is returned.
select field('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
field
-------
2
(1 row)
Description
Index (position) of first argument within second argument.
Syntax
FIND_IN_SET(str "any",strlist TEXT) returns INTEGER
General rules
Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. strlist is a string composed of substrings separated by , characters.
Returns 0 if str is not in strlist or if strlist is the empty string.
Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (,) character.
If first argument is not String Types,the first parameter will be converted to string.In other words,find_in_set(n,strlist) is equivalent to find_in_set(n::text,strlist)
The data type of the return value is integer.
Example
In the following example,position of 'b' within 'a,b,c,d' is returned.
SELECT FIND_IN_SET('b','a,b,c,d');
find_in_set
-------------
2
(1 row)
Description
Decode base64 encoded string and return result.
Syntax
from_base64(p_str text) RETURNS text
General rules
Takes a string encoded with the base-64 encoded rules used by TO_BASE64() and returns the decoded result as a binary string.
The result is NULL if the argument is NULL or not a valid base-64 string.
See the description of TO_BASE64() for details about the encoding and decoding rules.
Example
In the following example,Decode base64 encoded string 'YWJj' and return result 'abc'.
select from_base64('YWJj');
from_base64
-------------
abc
(1 row)
Description
Insert substring at specified position up to specified number of characters.
Syntax
insert(p_source text, p_pos bigint, p_len bigint, p_replacement text) RETURNS text
General rules
Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr.
Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string.
Returns NULL if any argument is NULL.
Example
In the following example,Insert substring 'What' at specified position 3 up to specified number 4 of characters.
select insert('Quadratic', 3, 4, 'What');
insert
-----------
QuWhattic
(1 row)
Description
Get the first occurrence position of the substring.
Syntax
instr(str text, patt text) RETURNS int
General rules
Get the first occurrence position of sub string patt in str, starting from 1.
If not found, return 0.
Example
In the following example, the first occurrence of substring '4de' in string 'abc124deff4de' is 6.
select instr('abc124deff4de', '4de');
instr
-------
6
(1 row)
Description
Synonym for LOWER().
Syntax
lcase(p_source text) RETURNS text
General rules
LCASE() is a synonym for LOWER().
Returns the string str with all characters changed to lowercase according to the current character set mapping. The default is utf8mb4.
Example
In the following example,Returns the string 'quadratic' with all characters changed to lowercase.
select lcase('QuadRatic');
lcase
-----------
quadratic
(1 row)
Description
Return the position of the first occurrence of substring.
Syntax
LOCATE(substr TEXT,str TEXT) returns INTEGER
LOCATE(substr TEXT,str TEXT,POS INTEGER) returns INTEGER
General rules
The first syntax returns the position of the first occurrence of substring substr in string str.
The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos.
Returns 0 if substr is not in str.
Returns NULL if any argument is NULL.
Example
In the following example,position of 'bar' in 'foobarbar', starting at position 5. is returned.
SELECT LOCATE('bar', 'foobarbar', 5);
locate
--------
7
(1 row)
Description
Return a substring starting from the specified position.
Syntax
mid(p_source text, p_pos bigint, p_len bigint) RETURNS text
General rules
The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos.
It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function. A value of 0 for pos returns an empty string.
Example
In the following example, return a substring 'ongy' starting from the specified position 2.
select mid('Hongye', 2, 4);
mid
------
ongy
(1 row)
Description
Return a string of the specified number of spaces.
Syntax
space(p_num int) RETURNS text
General rules
Returns a string consisting of N space characters.
Example
In the following example, return a string of the specified number of spaces.
select concat('|', space(11), '|');
concat
---------------
| |
(1 row)
Description
Compare two strings.
Syntax
strcmp(p1 text, p2 text) RETURNS int
General rules
STRCMP() returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise.
Example
In the following example, returns -1 the first argument is smaller than the second according to the current sort order.
select strcmp('text', 'text2');
strcmp
--------
-1
(1 row)
Description
Return the argument converted to a base-64 string.
Syntax
to_base64(p_str text) RETURNS text
General rules
Converts the string argument to base-64 encoded form and returns the result as a character string with the connection character set and collation.
If the argument is not a string, it is converted to a string before conversion takes place. The result is NULL if the argument is NULL.
Base-64 encoded strings can be decoded using the FROM_BASE64() function.
Example
In the following example, return the argument 'abc' converted to a base-64 string 'YWJj'.
select to_base64('abc');
to_base64
-----------
YWJj
(1 row)
Description
Synonym for UPPER().
Syntax
ucase(p_source text) RETURNS text
General rules
Returns the string str with all characters changed to uppercase according to the current character set mapping. The default is utf8mb4.
Example
In the following example, returns the string 'QUADRATIC' with all characters changed to uppercase according to the current character set mapping.
select ucase('QuadRatic');
ucase
-----------
QUADRATIC
(1 row)
Description
Return the substring as specified
Syntax
substr(str text, pos int) RETURNS text
substr(str text, pos int, len int) RETURNS text
General rules
The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos.
It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function. A value of 0 for pos returns an empty string.
This function is multibyte safe. It returns NULL if any of its arguments are NULL.
If len is less than 1, the result is the empty string.
It is also possible for both pos and len to accept numeric type, if the fractional part of a input less than 0.5, round to integer part, otherwise round to floor of input + 0.5 for a positive input, floor of intput-0.5 would be applied if input is negative.
Example
In the following example, the result is bc
select substr('abc',-2,2);
substr
--------
bc
(1 row)
Description
Return the substring as specified
Syntax
substring(str text, pos int) RETURNS text
substring(str text, pos int, len int) RETURNS text
General rules
substring is a synonym for substr
Example
In the following example, the result is bc
select substring('abc',-2,2);
substring
--------
bc
(1 row)
Description
Return a substring from a string before the specified number of occurrences of the delimiter
Syntax
substring_index(str varchar, delim varchar, count integer) RETURNS varchar
General rules
Returns the substring from string str before count occurrences of the delimiter delim.
If count is positive, everything to the left of the final delimiter (counting from the left) is returned.
If count is negative, everything to the right of the final delimiter (counting from the right) is returned.
SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.
This function is multibyte safe.
SUBSTRING_INDEX() returns NULL if any of its arguments are NULL.
When delim is '' return ''(same with mysql).
Example
lightdb@test_m=# SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
substring_index
-----------------
www.mysql
(1 row)
lightdb@test_m=# SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
substring_index
-----------------
mysql.com
(1 row)
The following date/time functions are supported:
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYOFWEEK
FROM_UNIXTIME
STR_TO_DATE
SYSDATE
SEC_TO_TIME
TIME_TO_SEC
TIMEDIFF
TIMESTAMPDIFF
TO_DAYS
TO_SECONDS
UNIX_TIMESTAMP
WEEKOFYEAR
YEAR
QUARTER
LAST_DAY
CURDATE
Description
Subtract two dates.
Syntax
DATEDIFF(expr1 timestamp,expr2 timestamp) RETURN integer
DATEDIFF(expr1 text,expr2 text) RETURN integer
DATEDIFF(expr1 timestamptz,expr2 timestamptz) RETURN integer
DATEDIFF(expr1 timestamptz,expr2 timestamp) RETURN integer
DATEDIFF(expr1 timestamp,expr2 timestamptz) RETURN integer
DATEDIFF(expr1 date, expr2 date) RETURN integer
DATEDIFF(expr1 date, expr2 numeric) RETURN integer
DATEDIFF(expr1 numeric, expr2 date) RETURN integer
DATEDIFF(expr1 numeric, expr2 numeric) RETURN integer
General rules
expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
Returns NULL if any argument is NULL.
Parameter can't be time type(mysql can be time type).
Example
In the following example, 1 is returned because '2007-12-31' minus '2007-12-30' equals 1
SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
datediff
----------
1
(1 row)
SELECT DATEDIFF(cast('2007-12-31 23:59:59' as datetime),cast('2007-12-30' as datetime));
datediff
----------
1
(1 row)
SELECT DATEDIFF(cast('2007-12-31 23:59:59' as date),cast('2007-12-30' as date));
datediff
----------
1
(1 row)
Description
Date addition.
Syntax
DATE_ADD(expr1 timestamptz, expr2 interval) RETURN date
DATE_ADD(expr1 text, expr2 interval) RETURN date
General rules
Returns the date type after adding interval (year / month / day) to the expr1 date parameter
The second parameter expr2 only support interval (year / month / day)
Example
In the following example, '2020-01-02' is returned because '20200101' add 1 day
select date_add('20200101', interval 1 day);
date_add
------------
2020-01-02
(1 row)
Description
Date format display.
Syntax
DATE_FORMAT(expr1 timestamp, expr2 text) RETURN text
DATE_FORMAT(expr1 timestamptz, expr2 text) RETURN text
DATE_FORMAT(expr1 text, expr2 text) RETURN text
General rules
Return expr1 time parameter, display and output in expr2 format
Example
In the following example, '2021/12/01 11,59,59' is returned because expr2is in '%Y/%m/%d %h,%i,%s' format
select date_format('20211201 11:59:59','%Y/%m/%d %h,%i,%s');
date_format
---------------------
2021/12/01 11,59,59
(1 row)
Table 9.107 shows the template patterns available for formatting date_format values.
Table 9.107. Template Patterns for date_format Formatting
| Pattern | Description |
|---|---|
%a | Abbreviated week name |
%b | Abbreviated month name |
%c | Month, value |
%D | Day of the month with English prefix |
%d | Day of month, value (00-31) |
%e | Day of month, value (0-31) |
%f | Microsecond |
%H | Hours (00-23) |
%h | Hours (01-12) |
%I | Hours (01-12) |
%i | Minute, value (00-59) |
%j | Day of year (001-366) |
%k | Hours (0-23) |
%l | Hours (1-12) |
%M | Month Name |
%m | Month, value (00-12) |
%p | AM or PM |
%r | Time, 12 hours (hh: mm: ss AM or PM) |
%S | Seconds (00-59) |
%s | Seconds (00-59) |
%T | Time, 24 hours (hh: mm: ss) |
%W | Week Name |
%Y | Year, 4 digits |
%y | Year, 2 digits |
Description
Date subtraction.
Syntax
DATE_SUB(expr1 timestamptz, expr2 interval) RETURN date
DATE_SUB(expr1 text, expr2 interval) RETURN date
General rules
Returns the date type after the expr1 date parameter minus the interval (year / month / day)
The second parameter expr2 only support interval (year / month / day)
Example
In the following example, '2019-12-31' is returned because '20200101' minus 1 day
select date_sub('20200101', interval 1 day);
date_sub
------------
2019-12-31
(1 row)
Description
Return month date.
Syntax
DAY(expr1 text) RETURN int2
DAY(expr1 timestamptz) RETURN int4
General rules
Returns the date part of a given expr1 date month
Only month and day parameters are not supported, such as' 0101 ', 0704
Example
In the following example, '1' is returned because 2011/12/01's date is 1
select day('2011/12/01');
day
-----
1
(1 row)
Description
Return to the day of the week.
Syntax
DAYOFWEEK(expr1 text) RETURN int4
DAYOFWEEK(expr1 timestamptz) RETURN int4
General rules
Returns the day of the week according to the expr1 date parameter. The return value is 1 ~ 7, and Sunday is 1
At least include the complete time format, such as 20200202, 200202. Parameters less than 6 bits are not supported
Example
In the following example, '4' is returned because 20200101 is Wednesday
select dayofweek(20200101);
dayofweek
-----------
4
(1 row)
Description
Return time format according to UNIX timestamp.
Syntax
FROM_UNIXTIME(expr1 text) RETURN timestamp
FROM_UNIXTIME(expr1 int8) RETURN timestamp
FROM_UNIXTIME(expr1 text, expr2 text) RETURN text
FROM_UNIXTIME(expr1 int8, expr2 text) RETURN text
General rules
Return time format according to expr1 parameter UNIX timestamp. The date format can be specified according to the expr2 parameter
Example
In the following example, timestamp is returned according to UNIX timestamp 20220910
select from_unixtime(20220910);
from_unixtime
---------------------
1970-08-23 08:55:10
(1 row)
select from_unixtime(20220910, '%Y/%m/%d %h,%i,%s');
from_unixtime
---------------------
1970/08/23 08,55,10
(1 row)
Table 9.108 shows the template patterns available for formatting from_unixtime values.
Table 9.108. Template Patterns for from_unixtime Formatting
| Pattern | Description |
|---|---|
%a | Abbreviated week name |
%b | Abbreviated month name |
%c | Month, value |
%D | Day of the month with English prefix |
%d | Day of month, value (00-31) |
%e | Day of month, value (0-31) |
%f | Microsecond |
%H | Hours (00-23) |
%h | Hours (01-12) |
%I | Hours (01-12) |
%i | Minute, value (00-59) |
%j | Day of year (001-366) |
%k | Hours (0-23) |
%l | Hours (1-12) |
%M | Month Name |
%m | Month, value (00-12) |
%p | AM or PM |
%r | Time, 12 hours (hh: mm: ss AM or PM) |
%S | Seconds (00-59) |
%s | Seconds (00-59) |
%T | Time, 24 hours (hh: mm: ss) |
%W | Week Name |
%Y | Year, 4 digits |
%y | Year, 2 digits |
Description
Converts seconds to 'hh:mm:ss' format.
Syntax
sec_to_time(p_secs int) RETURNS interval
General rules
Returns the seconds argument, converted to hours, minutes, and seconds, as a TIME value.
The range of the result is constrained to that of the TIME data type. A warning occurs if the argument corresponds to a value outside that range.
Example
In the following example, Converts seconds 80580 to 'hh:mm:ss' format 22:23:00.
select mysql.sec_to_time(80580);
sec_to_time
-------------
22:23:00
(1 row)
Description
Convert string to time value. This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date,time, or datetime value extracted from str is illegal, STR_TO_DATE() returns error.
Syntax
STR_TO_DATE(datetime_str text, datetime_format text) RETURN datetime
STR_TO_DATE(date_str text, date_format text) RETURN date
STR_TO_DATE(time_str text, time_format text) RETURN time
General rules
Convert string str parameter and format format parameter to time value
If the NO_ZERO_DATE SQL mode is enabled, zero dates are disallowed. In that case, STR_TO_DATE() returns NULL.
Example
In the following example, '2022-09-10 11:13:13' is returned according to timestamp '2022/09/10 11,13,13' and format '%Y/%m/%d %h,%i,%s'. It returns a DATETIME value if the format string contains both date and time parts.
select str_to_date('2022/09/10 11,13,13', '%Y/%m/%d %h,%i,%s');
str_to_date
---------------------
2022-09-10 11:13:13
(1 row)
In the following example, returns a DATE value because of the format string contains only date parts.
select str_to_date('2022/09/10', '%Y/%m/%d');
str_to_date
---------------------
2022-09-10
(1 row)
In the following example, returns a TIME value because of the format string contains only time parts.
select str_to_date('09:00:59', '%h:%i:%s');
str_to_date
---------------------
09:00:59
(1 row)
If the NO_ZERO_DATE SQL mode is enabled, zero dates are disallowed. In that case, STR_TO_DATE() return NULL:
set lightdb_sql_mode = 'no_zero_date';
select str_to_date('2022/09/0', '%Y/%m/%d');
str_to_date
-------------
(1 row)
Table 9.109 shows the template patterns available for formatting str_to_date values.
Table 9.109. Template Patterns for str_to_date Formatting
| Pattern | Description |
|---|---|
%a | Abbreviated week name |
%b | Abbreviated month name |
%c | Month, value |
%D | Day of the month with English prefix |
%d | Day of month, value (00-31) |
%e | Day of month, value (0-31) |
%f | Microsecond |
%H | Hours (00-23) |
%h | Hours (01-12) |
%I | Hours (01-12) |
%i | Minute, value (00-59) |
%j | Day of year (001-366) |
%k | Hours (0-23) |
%l | Hours (1-12) |
%M | Month Name |
%m | Month, value (00-12) |
%p | AM or PM |
%r | Time, 12 hours (hh: mm: ss AM or PM) |
%S | Seconds (00-59) |
%s | Seconds (00-59) |
%T | Time, 24 hours (hh: mm: ss) |
%W | Week Name |
%Y | Year, 4 digits |
%y | Year, 2 digits |
Description
Return the time at which the function executes.
Syntax
sysdate() RETURNS timestamp(0)
General rules
Returns the time at which it executes, it is nondeterministic.
The return value affacted by timezone setting.
Available since version 23.2
Examples as below:
select sysdate;
sysdate
---------------------
2023-05-06 16:06:59
(1 row)
Description
Return the argument converted to seconds.
Syntax
time_to_sec(p_time time) RETURNS int
General rules
Returns the time argument, converted to seconds.
Example
In the following example, returns the time argument '22:23:00' converted to seconds 80580.
select time_to_sec('22:23:00');
time_to_sec
-------------
80580
(1 row)
Description
Subtract time.
Syntax
timediff(p_date1 timestamptz, p_date2 timestamptz) RETURNS interval
General rules
TIMEDIFF() returns expr1 − expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.
The result returned by TIMEDIFF() is limited to the range allowed for TIME values. Alternatively, you can use either of the functions TIMESTAMPDIFF() and UNIX_TIMESTAMP(), both of which return integers.
Example
In the following example, returns '2000-01-01 00:00:00' − '2000-01-01 00:00:00.000001' expressed as a time value.
select mysql.timediff('2000-01-01 00:00:00', '2000-01-01 00:00:00.000001');
timediff
------------------
-00:00:00.000001
(1 row)
Description
Subtract an interval from a datetime expression.
Syntax
timestampdiff( p_unit text, p_ts1 text, p_ts2 text) RETURNS bigint
timestampdiff( p_unit text, p_ts1 text, p_ts2 timestamptz) RETURNS bigint
timestampdiff( p_unit text, p_ts1 timestamptz, p_ts2 text) RETURNS bigint
timestampdiff( p_unit text, p_ts1 timestamptz, p_ts2 timestamptz) RETURNS bigint
General rules
Returns datetime_expr2 − datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions.
One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary.
The unit for the result (an integer) is given by the unit argument. The unit parameters support MICROSECOND, SECOND, MINUTE,
HOUR, DAY, WEEK,
MONTH, QUARTER and YEAR, with or without quotes.
Example
In the following example, Returns '1911-11-11 11:23:45.123456' − '2021-12-12 12:12:12.654321'::timestamp, the result (an integer) is given by MICROSECOND.
select timestampdiff ('MICROSECOND', '1911-11-11 11:23:45.123456'::timestamp, '2021-12-12 12:12:12.654321'::timestamp);
timestampdiff
------------------
3474060507530865
(1 row)
select timestampdiff (MICROSECOND, '1911-11-11 11:23:45.123456'::timestamp, '2021-12-12 12:12:12.654321'::timestamp);
timestampdiff
------------------
3474060507530865
(1 row)
Description
Return the date argument converted to days.
Syntax
to_days(p_date timestamp) RETURNS bigint
to_days(p_date timestamptz) RETURNS bigint
to_days( p_date text) RETURNS bigint
to_days( p_date text, p_format text) RETURNS bigint
to_days(p_date bigint) RETURNS bigint
General rules
Given a date date, returns a day number (the number of days since year 0).
TO_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it does not take into account the days that were lost when the calendar was changed. For dates before 1582 (and possibly a later year in other locales), results from this function are not reliable.
Example
In the following example, return the date argument '1111-11-11' converted to days 406098.
select to_days('1111-11-11');
to_days
---------
406098
(1 row)
Description
Return the date or datetime argument converted to seconds since Year 0.
Syntax
to_seconds(p_date timestamp) RETURNS bigint
to_seconds(p_date timestamptz) RETURNS bigint
General rules
Given a date or datetime expr, returns the number of seconds since the year 0. If expr is not a valid date or datetime value, returns NULL.
Like TO_DAYS(), TO_SECONDS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it does not take into account the days that were lost when the calendar was changed. For dates before 1582 (and possibly a later year in other locales), results from this function are not reliable.
Example
In the following example, Return the date or datetime argument '2009-11-29' converted to seconds 63426672000 since Year 0.
select to_seconds('2009-11-29'::timestamp);
to_seconds
-------------
63426672000
(1 row)
Description
Return UNIX timestamp according to time format.
Syntax
UNIX_TIMESTAMP() RETURN int8
UNIX_TIMESTAMP(expr1 timestamptz) RETURN int8
UNIX_TIMESTAMP(expr1 text) RETURN int8
General rules
Returns the UNIX timestamp according to the expr1 parameter time format. If there is no parameter, returns the current UNIX timestamp
Expr1 parameter contains at least the complete log format, such as 20200101
Example
In the following example, Return the unix timestamp '1577808000' corresponding parameter 20200101.
select unix_timestamp(20200101);
unix_timestamp
----------------
1577808000
(1 row)
select unix_timestamp();
unix_timestamp
----------------
1663051334
(1 row)
Description
According to the time, return to the week within a year.
Syntax
WEEKOFYEAR(expr1 timestamptz) RETURN int4
WEEKOFYEAR(expr1 text) RETURN int4
General rules
According to the time expr1 parameter, return to the week within a year
At least include the complete time format, such as 20200202, 200202. Parameters less than 6 bits are not supported
Example
In the following example, '36' is returned because '20220910' is the 36th week of the year
select weekofyear('20220910');
weekofyear
------------
36
(1 row)
Description
Returns the year of the date.
Syntax
YEAR(expr1 timestamptz) RETURN int4
YEAR(expr1 text) RETURN int4
General rules
Returns the year according to the expr1 parameter date
It supports complete month, day and month time parameters, such as' 20200101 'and' 200101 '. It does not support only including month and day parameters, such as' 0101' and '0704'
Example
In the following example, '2020' is returned because the year of '20200101' is 2020
select year(20200101);
year
------
2020
(1 row)
select year('200101');
year
------
2020
(1 row)
Description
Returns the quarter of the year for date, in the range 1 to 4.
Syntax
QUARTER(timestamp) RETURN int4
General rules
NULL if date is NULL.
It one supports the timestamp type, and any type that can be implicitly converted to a timestamp type now(like date type).
Example
In the following example, '4' is returned because the quarter of '20201001' is 4
select quarter(cast('20201001'as date));
quarter
---------
4
(1 row)
select quarter(cast('20201001'as datetime));
quarter
---------
4
(1 row)
select quarter('20201001');
quarter
---------
4
(1 row)
Description
Returns the corresponding value for the last day of the month.
Syntax
LAST_DAY(date) RETURN date
LAST_DAY(text) RETURN date
General rules
Returns NULL if the argument is NULL.
Error if the argument is invalid.
It only supports date and text type(also includes any data type that can be implicitly converted to either type) now.
Example
In the following example, last day of the month is returned if parameter is valid:
lightdb@test_m=# SELECT LAST_DAY('2003-02-05');
last_day
------------
2003-02-28
(1 row)
lightdb@test_m=# SELECT LAST_DAY('2004-02-05');
last_day
------------
2004-02-29
(1 row)
lightdb@test_m=# SELECT LAST_DAY('2004-01-01 01:01:01');
last_day
------------
2004-01-31
(1 row)
lightdb@test_m=# SELECT LAST_DAY('2003-03-32');
ERROR: date/time field value out of range: "2003-03-32"
HINT: Perhaps you need a different "datestyle" setting.
CONTEXT: SQL function "last_day" statement 1
lightdb@test_m=#
Description
Returns the current date as a value in 'YYYY-MM-DD' format.
Syntax
CURDATE() RETURN date
General rules
Cannot return YYYYMMDD format like mysql yet, therefore, multiplication and division are not supported, addition and subtraction results are different from mysqls. Mysql will Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in string or numeric context.
Example
lightdb@test_m=# select curdate();
curdate
------------
2023-12-06
(1 row)
lightdb@test_m=# select curdate()+1;
?column?
------------
2023-12-07
(1 row)
lightdb@test_m=# select curdate()/2;
ERROR: operator does not exist: date / integer
LINE 1: select curdate()/2;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
lightdb@test_m=# select curdate()*2;
ERROR: operator does not exist: date * integer
LINE 1: select curdate()*2;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
lightdb@test_m=# select curdate()-2;
?column?
------------
2023-12-04
(1 row)
The following json functions are supported:
JSON_ARRAY
JSON_OBJECT
JSON_EXTRACT
JSON_CONTAINS
JSON_CONTAINS_PATH
JSON_PRETTY
JSON_REMOVE
JSON_INSERT
JSON_REPLACE
JSON_SET
Description
Evaluates a (possibly empty) list of values and returns a JSON array containing those values.
Syntax
JSON_ARRAY( VARIADIC "any" ) RETURNS jsonb
General rules
Returns a JSON array, the data type is jsonb.
Example
SELECT JSON_ARRAY(1, 'abc', NULL, TRUE, NOW());
json_array
------------------------------------------------------------
[1, "abc", null, true, "2023-05-31T19:44:44.881055+08:00"]
(1 row)
Description
Evaluates a (possibly empty) list of key-value pairs and returns a JSON object containing those pairs.
Syntax
JSON_OBJECT( VARIADIC "any" ) RETURNS jsonb
General rules
Returns a JSON object, the data type is jsonb.
An error occurs if any key name is NULL or the number of arguments is odd.
Example
SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
json_object
------------------------------
{"id": 87, "name": "carrot"}
(1 row)
SELECT JSON_OBJECT('id', 87, 'name', 'carrot', 'id', 99);
json_object
------------------------------
{"id": 99, "name": "carrot"}
(1 row)
Description
Returns data from a JSON document, selected from the parts of the document matched by the path arguments.
Syntax
JSON_EXTRACT(json_doc jsonb, path jsonpath) RETURNS jsonb
General rules
The return value consists of all values matched by the path
arguments, the data type is jsonb.
If it is possible that those arguments could return multiple values,
the matched values are autowrapped as an array, in the order
corresponding to the paths that produced them. Otherwise,
the return value is the single matched value.
Returns NULL if any argument is NULL or no paths locate a value in the document.
An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression.
Example
SELECT JSON_EXTRACT('{"k1":11,"k2":22,"k2 2":33}','$.k1');
json_extract
--------------
11
(1 row)
SELECT JSON_EXTRACT('{"k1":11,"k2":22,"k2 2":33}','$."k2 2"');
json_extract
--------------
33
(1 row)
SELECT JSON_EXTRACT('{"a":{"c":20, "d":{"b":{"b":30, "c":40}, "e":{"b":50}}}, "c":{"b":60, "c":70}, "b":80}','$.a.c');
json_extract
--------------
20
(1 row)
SELECT JSON_EXTRACT('[10,"str", null, true]','$[1]');
json_extract
--------------
"str"
(1 row)
SELECT JSON_EXTRACT('[10,"str", null, true]','$[last]');
json_extract
--------------
true
(1 row)
SELECT JSON_EXTRACT('[10,"str", null, true]','$[last-2]');
json_extract
--------------
"str"
(1 row)
In the following example, multiple values matched, they are autowrapped as an array.
SELECT JSON_EXTRACT('[10,"str", null, true]','$[1 to 3]');
json_extract
---------------------
["str", null, true]
(1 row)
SELECT JSON_EXTRACT('[10,"str", null, true]','$[*]');
json_extract
-------------------------
[10, "str", null, true]
(1 row)
SELECT JSON_EXTRACT('[10]','$[*]');
json_extract
--------------
[10]
(1 row)
SELECT JSON_EXTRACT('{"k1":1,"k2":"22","k2 2":[3,33]}','$.*');
json_extract
--------------------
[1, "22", [3, 33]]
(1 row)
Description
Indicates by returning true or false whether a given candidate JSON document is contained within a target JSON document.
Syntax
JSON_CONTAINS(json_doc jsonb, json_val jsonb, path jsonpath) RETURNS bool
General rules
Returns NULL if any argument is NULL, or if the path argument does not identify a section of the target document.
An error occurs if target or candidate is not a valid JSON document.
To check only whether any data exists at the path, use JSON_CONTAINS_PATH() instead.
Example
SELECT JSON_CONTAINS('{"a": 111, "b": 222, "c": {"d": 4444}}', '111', '$.a');
json_contains
---------------
t
(1 row)
SELECT JSON_CONTAINS('{"a": "111", "b": 222, "c": {"d": 4444}}', '111', '$.a');
json_contains
---------------
f
(1 row)
SELECT JSON_CONTAINS('{"a": "111", "b": 222, "c": {"d": 4444}}', '" 111"', '$.a');
json_contains
---------------
f
(1 row)
SELECT JSON_CONTAINS('{"a": 111, "b": 222, "c": {"d": 4444}}', '{"d" : 4444 }', '$.c');
json_contains
---------------
t
(1 row)
Description
Returns true or false to indicate whether a JSON document contains data at a given path or paths.
Syntax
JSON_CONTAINS_PATH(json_doc jsonb, one_or_all text, variadic jsonpath[]) RETURNS bool
General rules
The return value is false if no specified path exists within the document. Otherwise, the return value depends on the one_or_all argument:
'one' : true if at least one path exists within the document, false otherwise.
'all' : true if all paths exist within the document, false otherwise.
An error occurs if the json_doc argument is not a valid JSON document, any path argument is not a valid path expression, or one_or_all is not 'one' or 'all'.
To check for a specific value at a path, use JSON_CONTAINS() instead.
Example
SELECT JSON_CONTAINS_PATH('{"a": 111, "b": 222, "c": {"d": 4444}}', 'one', '$.a', '$.z');
json_contains_path
--------------------
t
(1 row)
SELECT JSON_CONTAINS_PATH('{"a": 111, "b": 222, "c": {"d": 4444}}', 'one', '$.d');
json_contains_path
--------------------
f
(1 row)
SELECT JSON_CONTAINS_PATH('{"a": 111, "b": 222, "c": {"d": 4444}}', 'all', '$a', '$.b', '$.c');
json_contains_path
--------------------
t
(1 row)
SELECT JSON_CONTAINS_PATH('{"a": 111, "b": 222, "c": {"d": 4444}}', 'all', '$a', '$.b', '$.d');
json_contains_path
--------------------
f
(1 row)
Description
Provides pretty-printing of JSON values similar to that implemented in PHP and by other languages and database systems.
Syntax
JSON_PRETTY(json_doc jsonb) RETURNS text
General rules
The data type of return value is text.
The value supplied must be a JSON value or a valid string representation of a JSON value. Extraneous whitespaces and newlines present in this value have no effect on the output.
For a NULL value, the function returns NULL. If the value is not a JSON document, or if it cannot be parsed as one, the function fails with an error.
Example
SELECT JSON_PRETTY('{"a":{"b":{"z":1},"c":[5,{"z":2}],"z":3}, "z":4}');
json_pretty
------------------------
{ +
"a": { +
"b": { +
"z": 1 +
}, +
"c": [ +
5, +
{ +
"z": 2+
} +
], +
"z": 3 +
}, +
"z": 4 +
}
(1 row)
Description
Removes data from a JSON document and returns the result.
Syntax
JSON_REMOVE(jsonb, jsonpath) RETURNS jsonb
General rules
Removes data from a JSON document and returns the result. Returns NULL if any argument is NULL.
An error occurs if the jsonb argument is not a valid JSON document or any path argument is not a valid path expression or is $ or contains a * or ** wildcard.
It is not an error if the element to be removed does not exist in the document; in that case, the path does not affect the document.
Example
SELECT JSON_REMOVE('{"a": 1, "b": [1,2,3], "c": {"d": 4}}', '$.c.d');
json_remove
-----------------------------------
{"a": 1, "b": [1, 2, 3], "c": {}}
(1 row)
SELECT JSON_REMOVE('{"a": 1, "b": [1,2,3], "c": {"d": 4}}', '$.b[last]');
json_remove
--------------------------------------
{"a": 1, "b": [1, 2], "c": {"d": 4}}
(1 row)
SELECT JSON_REMOVE('{"a": 1, "b": [1,2,3], "c": {"d": 4}}', '$.c.z');
json_remove
-----------------------------------------
{"a": 1, "b": [1, 2, 3], "c": {"d": 4}}
(1 row)
Different from MySQL, when accessing a JSON object through period
., if the object name is not enclosed in double
quotes and lt is an valid ECMAScript identifier name with some special
characters (like $), LightDB will prompt an error.
SELECT JSON_REMOVE('{"a": 1, "b": [1,2,3], "c": {"d": 4}}', '$.$z');
Description
Inserts data into a JSON document and returns the result.
Syntax
JSON_INSERT(jsonb, jsonpath, anyelement) RETURNS jsonb
JSON_INSERT(jsonb, jsonpath, text) RETURNS jsonb
General rules
Inserts data into a JSON document and returns the result. Returns NULL if any argument is NULL.
An error occurs if the jsonb argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.
A path-value pair for an existing path in the document is ignored and does not overwrite the existing document value. A path-value pair for a nonexisting path in the document adds the value to the document.
Example
SELECT JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.a', 10);
json_insert
-----------------------
{"a": 1, "b": [2, 3]}
(1 row)
SELECT JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.c', 200);
json_insert
---------------------------------
{"a": 1, "b": [2, 3], "c": 200}
(1 row)
Description
Replaces existing values in a JSON document and returns the result.
Syntax
JSON_REPLACE(jsonb, jsonpath, anyelement) RETURNS jsonb
JSON_REPLACE(jsonb, jsonpath, text) RETURNS jsonb
General rules
Replaces existing values in a JSON document and returns the result. Returns NULL if any argument is NULL.
An error occurs if the jsonb argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.
A path-value pair for an existing path in the document overwrites the existing document value with the new value. A path-value pair for a nonexisting path in the document is ignored and has no effect.
Example
SELECT JSON_REPLACE('{ "a": 1, "b": [2, 3]}', '$.a', 10);
json_replace
------------------------
{"a": 10, "b": [2, 3]}
(1 row)
SELECT JSON_REPLACE('{ "a": 1, "b": [2, 3]}', '$.c', 'null');
json_replace
----------------------------
{"a": 1, "b": [2, 3]}
(1 row)
SELECT JSON_REPLACE('100', '$','{ "a": 1, "b": [2, 3]}'::jsonb);
json_replace
-----------------------
{"a": 1, "b": [2, 3]}
(1 row)
Different from MySQL, when accessing a JSON object through period
., if the object name is not enclosed in double
quotes and lt is an illegal ECMAScript identifier name, LightDB will
not prompt an error but return the original value.
SELECT JSON_REPLACE('{ "a": 1, "b": [2, 3]}', '$.b.2z', 4);
json_replace
-----------------------
{"a": 1, "b": [2, 3]}
(1 row)
Description
Inserts or updates data in a JSON document and returns the result.
Syntax
JSON_SET(jsonb, jsonpath, anyelement) RETURNS jsonb
JSON_SET(jsonb, jsonpath, text) RETURNS jsonb
General rules
Inserts or updates data in a JSON document and returns the result. Returns NULL if json_doc or path is NULL.
An error occurs if the jsonb argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.
A path-value pair for an existing path in the document overwrites the existing document value with the new value. A path-value pair for a nonexisting path in the document adds the value to the document.
Example
SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10);
json_set
------------------------
{"a": 10, "b": [2, 3]}
(1 row)
SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.c', '[true, false]');
json_set
---------------------------------------------
{"a": 1, "b": [2, 3], "c": "[true, false]"}
(1 row)
SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.b.z', 4);
json_set
-----------------------
{"a": 1, "b": [2, 3]}
(1 row)
The following functions for making comparisons are supported:
IF
Description
If expr1 is TRUE, IF() returns expr2. Otherwise, it returns expr3.
Syntax
IF(expr1,expr2,expr3)
General rules
If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is "text".
If one of expr2 and expr3 is a string, the result type of the IF() function is "text", otherwise it is "numeric".
The following data types can be used in expr2 and expr3:
CHAR
VARCHAR
TEXT
INTEGER
BIGINT
SMALLINT
NUMERIC
REAL
DOUBLE PRECISION
BOOLEAN
TIMESTAMPTZ
Example
In the following example, 3 is returned because the value of 1 > 2 is false.
SELECT IF(1>2,2,3);
if
----
3
(1 row)
The following aggregation functions are supported:
ANY_VALUE
GROUP_CONCAT
Description
For compatibility with MySQL mode non ONLY_FULL_GROUP_BY syntax behavior of group by in by mode.
Syntax
ANY_VALUE(anyelement)
General rules
This function is useful for in MySQL compatible GROUP BY queries when the ONLY_FULL_GROUP_BY SQL mode is enabled, for cases when MySQL rejects a query that you know is valid for reasons that MySQL cannot determine, Default ANY_VALUE will return the first line causing ambiguity. The function return value and type are the same as the return value and type of its argument.
Note that this behavior is only a nonstandard syntax compatible with MySQL. It is used to ensure that the execution in LightDB will not report errors, but the results cannot be guaranteed to be completely consistent with MySQL. It is not recommended to use this feature. Please use the standard syntax format.
Example
In the following example, if id is a nonindexed column, the following query fails:
select co1, co2, max(co3) from t1 group by id;
ERROR: column "t1.co1" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select co1, co2, max(co3) from t1 group by id;
^
Use ANY_VALUE() to refer to co1 and co2:
select any_value(co1) as co1, any_value(co2) as co2, max(co3) from t1 group by id;
co1 | co2 | max
-----+-----+-----
6 | 9 | 30
4 | 5 | 5
3 | 3 | 4
2 | 3 | 4
(4 rows)
Description
Returns a concatenated, delimited list of string values.
Syntax
GROUP_CONCAT( [DISTINCT] column_name1 [ORDER BY {unsigned_integer | column_name2 | expr} [ASC | DESC] [SEPARATOR delimiter TEXT]) returns TEXT
General rules
This function returns a string result with the concatenated non-NULL values from a group.
It returns NULL if there are no non-NULL values.
To sort values in the result, use the ORDER BY clause.To sort in reverse order, add the DESC (descending) keyword to the name of the column you are sorting by in the ORDER BY clause. The default is ascending order; this may be specified explicitly using the ASC keyword.
To eliminate duplicate values, use the DISTINCT clause.
The default separator between values in a group is comma (,). To specify a separator explicitly, use SEPARATOR followed by the string literal value that should be inserted between group values. To eliminate the separator altogether, specify SEPARATOR ''.
Example
In the following example, the result with values of column col1 in table t delimited by ':' is returned.
select GROUP_CONCAT(col1 separator ':') from (values('BBBB',1),('AAAA',1),('CCCC',2)) as t(col1 ,col2) group by col2;
group_concat
--------------
BBBB:AAAA
CCCC
(2 rows)
The following System Information functions are supported:
DATABASE
Description
Synonym for current_schema(). The concept of schema is similar to that of database in MySQL. You can specify the current schema by setting the search_path.
Syntax
DATABASE() returns TEXT
General rules
Returns the name of the schema that is first in the search path (or a null value if the search path is empty).
Like mysql,This is the schema that will be used for any tables or other named objects that are created without specifying a target schema.
Example
In the following example, current schema is lightdb.
select database();
database
----------
lighdb
(1 row)
The following Miscellaneous Functions are supported:
BIN_TO_UUID
COMPRESS
UNCOMPRESS
UUID
UUID_TO_BIN
Description
Convert binary UUID to string.
Syntax
bin_to_uuid(p_uuid bytea, swap_flag int4 default 0) RETURNS uuid
General rules
BIN_TO_UUID() is the inverse of UUID_TO_BIN(). It converts a binary UUID to a string UUID and returns the result. The binary value should be a UUID as a VARBINARY(16) value.
The return value is a utf8 string of five hexadecimal numbers separated by dashes. (For details about this format, see the UUID() function description.) If the UUID argument is NULL, the return value is NULL. If any argument is invalid, an error occurs.
Example
In the following example, Convert binary UUID '\x0768a47dd355372b4926ee668b6cb443' to string '0768a47d-d355-372b-4926-ee668b6cb443'.
select bin_to_uuid('\x0768a47dd355372b4926ee668b6cb443');
bin_to_uuid
--------------------------------------
0768a47d-d355-372b-4926-ee668b6cb443
(1 row)
Description
Compress string and return binary data.
Syntax
COMPRESS(expr1 text) RETURN bytea
General rules
Compress string expr1 parameter and return binary data. The corresponding decompression function is UNCOMPRESS
Example
In the following example, compress 'abc123' into binary data '\x06000000616263313233'.
select compress('abc123');
compress
------------------------
\x06000000616263313233
(1 row)
Description
Decompress the binary data compressed by compress() and restore the original string.
Syntax
UNCOMPRESS(expr1 bytea) RETURN text
General rules
Decompress the binary data expr1 parameter compressed by compress() and restore the original string
Example
In the following example, decompress binary data '\x06000000616263313233' into bCompress 'abc123'
select uncompress(compress('abc123'));
uncompress
------------
abc123
(1 row)
Description
returns a randomly generated UUID, as a string of 36 characters.
Syntax
uuid() RETURNS text
General rules
UUID() returns a value that conforms to UUID version 4 as described in RFC 4122. The value is a 128-bit number represented as a utf8 string of five hexadecimal numbers in aaaaaaaa-bbbbcccc- dddd-eeeeeeeeeeee format.
Example
In the following example, generate random UUID.
select uuid();
uuid
--------------------------------------
f31f29e5-481f-4666-aa74-8cee3aa44545
(1 row)
Description
Convert string UUID to binary.
Syntax
uuid_to_bin(p_uuid uuid, swap_flag int4 default 0) RETURNS bytea
General rules
Converts a string UUID to a binary UUID and returns the result. (The IS_UUID() function description lists the permitted string UUID formats.)
The return binary UUID is a VARBINARY(16) value. If the UUID argument is NULL, the return value is NULL. If any argument is invalid, an error occurs.
Example
In the following example, Convert string UUID '0768a47d-d355-372b-4926-ee668b6cb443' to binary '\x0768a47dd355372b4926ee668b6cb443'.
select uuid_to_bin('0768a47d-d355-372b-4926-ee668b6cb443'::uuid)::text;
uuid_to_bin
------------------------------------
\x0768a47dd355372b4926ee668b6cb443
(1 row)
The following Operators are supported:
IFNULL
ISNULL
Description
The corresponding value is returned according to whether the parameter is empty.
Syntax
IFNULL(expr1 anycompatible, expr2 anycompatible) RETURNS anycompatible
IFNULL(expr1 text, expr2 text) RETURNS text
General rules
Judge whether the first parameter is null. If it is null, the value of the second parameter will be returned. If it is not null, the value of the first parameter will be returned
The two input parameters must belong to the same category, and the typcategory field of pg_type can be determined
Example
In the following example, when the first parameter is null, the second parameter 15 is returned; otherwise, the first parameter 1 is returned
select ifnull(1, 15);
ifnull
--------
1
(1 row)
select ifnull(null, 15);
ifnull
--------
15
(1 row)
Description
Test whether the argument is NULL
Syntax
isnull(text) RETURNS bool
isnull(numeric) RETURNS bool
isnull(timestamptz) RETURNS bool
General rules
If expr is NULL, ISNULL() returns true, otherwise it returns false.
Example
In the following example, expr is not NULL, ISNULL() returns false.
select isnull(0)::text;
isnull
--------
false
(1 row)
The following arithmetic operator are supported:
/
Description
Division operator. Division by zero produces a NULL result, reported a warning log.
Example
Division operator examples. Division by zero produces a NULL result, reported a warning log.
select 2 / 4;
?column?
------------------------
0.50000000000000000000
(1 row)
select 1.0 / 2;
?column?
------------------------
0.50000000000000000000
(1 row)
select 2 / 0;
WARNING: division by zero
?column?
----------
(1 row)
The following compare operators are supported:
=
!=
Description
Equal operator, support for varchar with bool or text. Got true for equality and false for non-equality.
Example
select '1'::varchar = true;
?column?
----------
t
(1 row)
select '1'::varchar = '1'::text;
?column?
----------
t
(1 row)
create table a(b varchar(5), c int);
insert into a values(true, 1);
insert into a values('true', 2);
select * from a;
b | c
------+---
1 | 1
true | 2
(2 rows)
select * from a where b = true;
b | c
---+---
1 | 1
(1 row)
select * from a where b = 'true';
b | c
------+---
true | 2
(1 row)
Description
Non-equal operator, support for varchar with bool or text. Got true for non-equality and false for equality.
Example
select '1' != true;
?column?
----------
f
(1 row)
select '1'::varchar != '1'::text;
?column?
----------
f
(1 row)
create table a(b varchar(5), c int);
insert into a values(true, 1);
insert into a values('true', 2);
select * from a;
b | c
------+---
1 | 1
true | 2
(2 rows)
select * from a where b != true;
b | c
------+---
true | 2
(1 row)
select * from a where b != 'true';
b | c
---+---
1 | 1
(1 row)
Description
Previously, bool type would transform to varchar kept invarient as aspect, like true to 'true' or false to 'false'. Myfce changes the rules above, transform bool type true to string '1' and false to string '0' for compatible with mysql.
Example
create table tb (a varchar(5), b int);
insert into tb values('true', 2); -- insert text 'true' as expected
insert into tb values(true, 1); -- insert true of bool type, expected to insert text '1' instead
insert into tb values(1, 3); -- insert numeric 1, expected to insert text '1' instead
insert into tb values('1', 4); -- insert text '1' as expected
insert into tb values(false, 5); -- insert false of bool type, expected to insert text '0'
insert into tb values('false', 6); -- insert text 'false' as expected
insert into tb values(0, 7); -- insert numeric 0, expected to insert text '0' instead
insert into tb values('0', 8); -- insert text '0' as expected
select * from tb;
a | b
-------+---
true | 2
1 | 1
1 | 3
1 | 4
0 | 5
false | 6
0 | 7
0 | 8
(8 rows)
Description
transform expr to unsigned bigint value.
General rules
Return type is numeric type. It is different from mysql, mysql is unsigned bigint type.
Expr of Date/Time types is not supported to cast yet. See examples for details.
Floating-point number in character types is not truncated when cast like mysql（'1.9' truncate to 1）. See examples for details.
Negative floating-point constant is not truncated when cast like mysql8.0, but mysql5.7 truncate it (floating-point number in col is not truncated). See examples for details.
Negative constant is truncated to 9223372036854775808 when overflowed like mysql8.0(mysql5.7 is 0). See examples for details.
Support casting strings in scientific notation format to unsigned, like '2e2' to 200(it is different from mysql, mysql is 2 after cast). See examples for details.
Example
lightdb@test_m=# select cast(123 as unsigned) as unsigned;
unsigned
----------
123
(1 row)
lightdb@test_m=# select cast(-123 as unsigned) as unsigned;
unsigned
----------------------
18446744073709551493
(1 row)
lightdb@test_m=# select cast(18446744073709551615 as unsigned) as unsigned;
unsigned
----------------------
18446744073709551615
(1 row)
lightdb@test_m=# select cast(18446744073709551616 as unsigned) as unsigned;
WARNING: unsigned bigint out of range, Truncated
unsigned
----------------------
18446744073709551615
(1 row)
lightdb@test_m=# select cast(-9223372036854775808 as unsigned) as unsigned;
unsigned
---------------------
9223372036854775808
(1 row)
-- mysql5.7为 0 ，mysql8.0: 9223372036854775808
lightdb@test_m=# select cast(-9223372036854775809 as unsigned) as unsigned;
WARNING: unsigned bigint out of range, Truncated
unsigned
---------------------
9223372036854775808
(1 row)
lightdb@test_m=# select cast(1.4 as unsigned) as unsigned;
unsigned
----------
1
(1 row)
lightdb@test_m=# select cast(1.5 as unsigned) as unsigned;
unsigned
----------
2
(1 row)
lightdb@test_m=# select cast(1.9 as unsigned) as unsigned;
unsigned
----------
2
(1 row)
-- mysql5.7: 0 mysql8.0: 18446744073709551614
lightdb@test_m=# select cast(-1.9 as unsigned) as unsigned;
unsigned
----------------------
18446744073709551614
(1 row)
-- mysql5.7: 0 mysql8.0: 18446744073709551614
lightdb@test_m=# select cast(-1.5 as unsigned) as unsigned;
unsigned
----------------------
18446744073709551614
(1 row)
-- mysql5.7: 0 mysql8.0: 18446744073709551615
lightdb@test_m=# select cast(-1.4 as unsigned) as unsigned;
unsigned
----------------------
18446744073709551615
(1 row)
-- mysql: 1
lightdb@test_m=# select cast('1.4' as unsigned) as unsigned;
unsigned
----------
1
(1 row)
-- mysql: 1
lightdb@test_m=# select cast('1.5' as unsigned) as unsigned;
unsigned
----------
2
(1 row)
-- mysql: 1
lightdb@test_m=# select cast('1.9' as unsigned) as unsigned;
unsigned
----------
2
(1 row)
-- mysql: 18446744073709551615
lightdb@test_m=# select cast('-1.4' as unsigned) as unsigned;
unsigned
----------------------
18446744073709551615
(1 row)
-- mysql: 18446744073709551615
lightdb@test_m=# select cast('-1.5' as unsigned) as unsigned;
unsigned
----------------------
18446744073709551614
(1 row)
-- mysql: 18446744073709551615
lightdb@test_m=# select cast('-1.9' as unsigned) as unsigned;
unsigned
----------------------
18446744073709551614
(1 row)
lightdb@test_m=# select cast(2e2 as unsigned) as unsigned;
unsigned
----------
200
(1 row)
lightdb@test_m=# select cast('2e2' as unsigned) as unsigned;
unsigned
----------
200
(1 row)
lightdb@test_m=# create table test_c(key1 float4);
CREATE TABLE
lightdb@test_m=# insert into test_c values('-1.6');
INSERT 0 1
lightdb@test_m=# insert into test_c values('-1.4');
INSERT 0 1
lightdb@test_m=# insert into test_c values('-1.5');
INSERT 0 1
lightdb@test_m=# select cast(key1 as unsigned) , key1 from test_c;
numeric2unsigned | key1
----------------------+------
18446744073709551614 | -1.6
18446744073709551615 | -1.4
18446744073709551614 | -1.5
(3 rows)
lightdb@test_m=# select cast(cast('20121212' as date) as unsigned);
ERROR: cannot cast type date to numeric
LINE 1: select cast(cast('20121212' as date) as unsigned);
^
lightdb@test_m=#
The following AES encryption and decryption and hexadecimal conversion operators are supported:
hex
unhex
aes_encrypt
aes_decrypt
Description
The hex function converts bytea data to text.
Syntax
hex(data bytea) returns text
Example
select hex('\x0123456789abcdef');
hex
------------------
0123456789abcdef
(1 row)
Description
The unhex function converts varchar data to bytea.
Syntax
unhex(data varchar) returns bytea
Example
select unhex('0123456789abcdef');
unhex
--------------------
\x0123456789abcdef
(1 row)
Description
AES encryption function.
Syntax
aes_encrypt(data bytea, key bytea) returns bytea
aes_encrypt(data varchar, key varchar) returns bytea
Example
select aes_encrypt('world', '123456');
aes_encrypt
------------------------------------
\x85400d3bbeeeb51259f497fcb4bee03e
(1 row)
Description
AES decryption function.
Syntax
aes_decrypt(data bytea, key bytea) returns bytea
aes_decrypt(data varchar, key varchar) returns bytea
aes_decrypt(data bytea, key varchar) returns bytea
Example
select aes_decrypt(aes_encrypt('world', '123456'), '123456');
aes_decrypt
--------------
\x776f726c64
(1 row)