Scalar functions¶
Scalar functions are functions that return scalars.
Table of contents
String functions¶
concat('first_arg', second_arg, [ parameter , ... ])
¶
Concatenates a variable number of arguments into a single string. It ignores
NULL
values.
Returns: text
cr> select concat('foo', null, 'bar') AS col;
+--------+
| col |
+--------+
| foobar |
+--------+
SELECT 1 row in set (... sec)
You can also use the ||
operator:
cr> select 'foo' || 'bar' AS col;
+--------+
| col |
+--------+
| foobar |
+--------+
SELECT 1 row in set (... sec)
format('format_string', parameter, [ parameter , ... ])
¶
Formats a string similar to the C function printf
. For details about the
format string syntax, see formatter
Returns: text
cr> select format('%s.%s', schema_name, table_name) AS fqtable
... from sys.shards
... where table_name = 'locations'
... limit 1;
+---------------+
| fqtable |
+---------------+
| doc.locations |
+---------------+
SELECT 1 row in set (... sec)
cr> select format('%tY', date) AS year
... from locations
... group by format('%tY', date)
... order by 1;
+------+
| year |
+------+
| 1979 |
| 2013 |
+------+
SELECT 2 rows in set (... sec)
substr('string', from, [ count ])
¶
Extracts a part of a string. from
specifies where to start and count
the length of the part.
Returns: text
cr> select substr('crate.io', 3, 2) AS substr;
+--------+
| substr |
+--------+
| at |
+--------+
SELECT 1 row in set (... sec)
char_length('string')
¶
Counts the number of characters in a string.
Returns: integer
cr> select char_length('crate.io') AS char_length;
+-------------+
| char_length |
+-------------+
| 8 |
+-------------+
SELECT 1 row in set (... sec)
Each character counts only once, regardless of its byte size.
cr> select char_length('©rate.io') AS char_length;
+-------------+
| char_length |
+-------------+
| 8 |
+-------------+
SELECT 1 row in set (... sec)
length(text)
¶
Returns the number of characters in a string.
The same as char_length.
bit_length('string')
¶
Counts the number of bits in a string.
Returns: integer
Note
CrateDB uses UTF-8 encoding internally, which uses between 1 and 4 bytes per character.
cr> select bit_length('crate.io') AS bit_length;
+------------+
| bit_length |
+------------+
| 64 |
+------------+
SELECT 1 row in set (... sec)
cr> select bit_length('©rate.io') AS bit_length;
+------------+
| bit_length |
+------------+
| 72 |
+------------+
SELECT 1 row in set (... sec)
octet_length('string')
¶
Counts the number of bytes (octets) in a string.
Returns: integer
cr> select octet_length('crate.io') AS octet_length;
+--------------+
| octet_length |
+--------------+
| 8 |
+--------------+
SELECT 1 row in set (... sec)
cr> select octet_length('©rate.io') AS octet_length;
+--------------+
| octet_length |
+--------------+
| 9 |
+--------------+
SELECT 1 row in set (... sec)
ascii(string)
¶
Returns the ASCII code of the first character. For UTF-8, returns the Unicode code point of the characters.
Returns: int
cr> SELECT ascii('a') AS a, ascii('🎈') AS b;
+----+--------+
| a | b |
+----+--------+
| 97 | 127880 |
+----+--------+
SELECT 1 row in set (... sec)
chr(int)
¶
Returns the character with the given code. For UTF-8 the argument is treated as a Unicode code point.
Returns: string
cr> SELECT chr(65) AS a;
+---+
| a |
+---+
| A |
+---+
SELECT 1 row in set (... sec)
lower('string')
¶
Converts all characters to lowercase. lower
does not perform
locale-sensitive or context-sensitive mappings.
Returns: text
cr> select lower('TransformMe') AS lower;
+-------------+
| lower |
+-------------+
| transformme |
+-------------+
SELECT 1 row in set (... sec)
upper('string')
¶
Converts all characters to uppercase. upper
does not perform
locale-sensitive or context-sensitive mappings.
Returns: text
cr> select upper('TransformMe') as upper;
+-------------+
| upper |
+-------------+
| TRANSFORMME |
+-------------+
SELECT 1 row in set (... sec)
initcap('string')
¶
Converts the first letter of each word to upper case and the rest to lower case (capitalize letters).
Returns: text
cr> select initcap('heLlo WORLD') AS initcap;
+-------------+
| initcap |
+-------------+
| Hello World |
+-------------+
SELECT 1 row in set (... sec)
sha1('string')
¶
Returns: text
Computes the SHA1 checksum of the given string.
cr> select sha1('foo') AS sha1;
+------------------------------------------+
| sha1 |
+------------------------------------------+
| 0beec7b5ea3f0fdbc95d0dd47f3c5bc275da8a33 |
+------------------------------------------+
SELECT 1 row in set (... sec)
md5('string')
¶
Returns: text
Computes the MD5 checksum of the given string.
See sha1 for an example.
replace(text, from, to)
¶
Replaces all occurrences of from
in text
with to
.
cr> select replace('Hello World', 'World', 'Stranger') AS hello;
+----------------+
| hello |
+----------------+
| Hello Stranger |
+----------------+
SELECT 1 row in set (... sec)
translate(string, from, to)
¶
Performs several single-character, one-to-one translation in one operation. It
translates string
by replacing the characters in the from
set,
one-to-one positionally, with their counterparts in the to
set. If from
is longer than to
, the function removes the occurrences of the extra
characters in from
. If there are repeated characters in from
, only the
first mapping is considered.
Synopsis:
translate(string, from, to)
Examples:
cr> select translate('Crate', 'Ct', 'Dk') as translation;
+-------------+
| translation |
+-------------+
| Drake |
+-------------+
SELECT 1 row in set (... sec)
cr> select translate('Crate', 'rCe', 'c') as translation;
+-------------+
| translation |
+-------------+
| cat |
+-------------+
SELECT 1 row in set (... sec)
trim({LEADING | TRAILING | BOTH} 'str_arg_1' FROM 'str_arg_2')
¶
Removes the longest string containing characters from str_arg_1
(' '
by
default) from the start, end, or both ends (BOTH
is the default) of
str_arg_2
.
Synopsis:
trim([ [ {LEADING | TRAILING | BOTH} ] [ str_arg_1 ] FROM ] str_arg_2)
Examples:
cr> select trim(BOTH 'ab' from 'abcba') AS trim;
+------+
| trim |
+------+
| c |
+------+
SELECT 1 row in set (... sec)
cr> select trim('ab' from 'abcba') AS trim;
+------+
| trim |
+------+
| c |
+------+
SELECT 1 row in set (... sec)
cr> select trim(' abcba ') AS trim;
+-------+
| trim |
+-------+
| abcba |
+-------+
SELECT 1 row in set (... sec)
ltrim(text, [ trimmingText ])
¶
Removes set of characters which are matching trimmingText
(' '
by
default) to the left of text
.
cr> select ltrim('xxxzzzabcba', 'xz') AS ltrim;
+-------+
| ltrim |
+-------+
| abcba |
+-------+
SELECT 1 row in set (... sec)
rtrim(text, [ trimmingText ])
¶
Removes set of characters which are matching trimmingText
(' '
by
default) to the right of text
.
cr> select rtrim('abcbaxxxzzz', 'xz') AS rtrim;
+-------+
| rtrim |
+-------+
| abcba |
+-------+
SELECT 1 row in set (... sec)
quote_ident(text)
¶
Returns: text
Quotes a provided string argument. Quotes are added only if necessary. For example, if the string contains non-identifier characters, keywords, or would be case-folded. Embedded quotes are properly doubled.
The quoted string can be used as an identifier in an SQL statement.
cr> select quote_ident('Column name') AS quoted;
+---------------+
| quoted |
+---------------+
| "Column name" |
+---------------+
SELECT 1 row in set (... sec)
left('string', len)
¶
Returns the first len
characters of string
when len
> 0, otherwise
all but last len
characters.
Synopsis:
left(string, len)
Examples:
cr> select left('crate.io', 5) AS col;
+-------+
| col |
+-------+
| crate |
+-------+
SELECT 1 row in set (... sec)
cr> select left('crate.io', -3) AS col;
+-------+
| col |
+-------+
| crate |
+-------+
SELECT 1 row in set (... sec)
right('string', len)
¶
Returns the last len
characters in string
when len
> 0, otherwise
all but first len
characters.
Synopsis:
right(string, len)
Examples:
cr> select right('crate.io', 2) AS col;
+-----+
| col |
+-----+
| io |
+-----+
SELECT 1 row in set (... sec)
cr> select right('crate.io', -6) AS col;
+-----+
| col |
+-----+
| io |
+-----+
SELECT 1 row in set (... sec)
lpad('string1', len[, 'string2'])
¶
Fill up string1
to length len
by prepending the characters string2
(a space by default). If string1
is already longer than len
then it is
truncated (on the right).
Synopsis:
lpad(string1, len[, string2])
Example:
cr> select lpad(' I like CrateDB!!', 41, 'yes! ') AS col;
+-------------------------------------------+
| col |
+-------------------------------------------+
| yes! yes! yes! yes! yes! I like CrateDB!! |
+-------------------------------------------+
SELECT 1 row in set (... sec)
rpad('string1', len[, 'string2'])
¶
Fill up string1
to length len
by appending the characters string2
(a space by default). If string1 is already longer than len
then it is
truncated.
Synopsis:
rpad(string1, len[, string2])
Example:
cr> select rpad('Do you like Crate?', 38, ' yes!') AS col;
+----------------------------------------+
| col |
+----------------------------------------+
| Do you like Crate? yes! yes! yes! yes! |
+----------------------------------------+
SELECT 1 row in set (... sec)
Note
In both cases, the scalar functions lpad
and rpad
do now accept a
length greater than 50000.
encode(bytea, format)
¶
Encode takes a binary string (hex
format) and returns a text encoding using
the specified format. Supported formats are: base64
, hex
, and
escape
. The escape
format replaces unprintable characters with octal
byte notation like \nnn
. For the reverse function, see decode().
Synopsis:
encode(string1, format)
Example:
cr> select encode(E'123\b\t56', 'base64') AS col;
+--------------+
| col |
+--------------+
| MTIzCAk1Ng== |
+--------------+
SELECT 1 row in set (... sec)
decode(text, format)
¶
Decodes a text encoded string using the specified format and returns a binary
string (hex
format). Supported formats are: base64
, hex
, and
escape
. For the reverse function, see encode().
Synopsis:
decode(text1, format)
Example:
cr> select decode('T\214', 'escape') AS col;
+--------+
| col |
+--------+
| \x548c |
+--------+
SELECT 1 row in set (... sec)
repeat(text, integer)
¶
Repeats a string the specified number of times.
If the number of repetitions is equal or less than zero then the function returns an empty string.
Returns: text
cr> select repeat('ab', 3) AS repeat;
+--------+
| repeat |
+--------+
| ababab |
+--------+
SELECT 1 row in set (... sec)
split_part(text, text, integer)
¶
Splits a string into parts using a delimiter and returns the part at the given
index. The first part is addressed by index 1
.
Special Cases:
Returns the empty string if the index is greater than the number of parts.
If any of the arguments is
NULL
, the result isNULL
.If the delimiter is the empty string, the input string is considered as consisting of exactly one part.
Returns: text
Synopsis:
split_part(string, delimiter, index)
Example:
cr> select split_part('ab--cdef--gh', '--', 2) AS part;
+------+
| part |
+------+
| cdef |
+------+
SELECT 1 row in set (... sec)
Date and time functions¶
date_trunc('interval', ['timezone',] timestamp)
¶
Returns: timestamp with time zone
Limits a timestamps precision to a given interval.
Valid intervals are:
second
minute
hour
day
week
month
quarter
year
Valid values for timezone
are either the name of a time zone (for example
‘Europe/Vienna’) or the UTC offset of a time zone (for example ‘+01:00’). To
get a complete overview of all possible values take a look at the available
time zones supported by Joda-Time.
The following example shows how to use the date_trunc
function to generate
a day based histogram in the Europe/Moscow
timezone:
cr> select
... date_trunc('day', 'Europe/Moscow', date) as day,
... count(*) as num_locations
... from locations
... group by 1
... order by 1;
+---------------+---------------+
| day | num_locations |
+---------------+---------------+
| 308523600000 | 4 |
| 1367352000000 | 1 |
| 1373918400000 | 8 |
+---------------+---------------+
SELECT 3 rows in set (... sec)
If you don’t specify a time zone, truncate
uses UTC time:
cr> select date_trunc('day', date) as day, count(*) as num_locations
... from locations
... group by 1
... order by 1;
+---------------+---------------+
| day | num_locations |
+---------------+---------------+
| 308534400000 | 4 |
| 1367366400000 | 1 |
| 1373932800000 | 8 |
+---------------+---------------+
SELECT 3 rows in set (... sec)
date_bin(interval, timestamp, origin)
¶
date_bin
“bins” the input timestamp to the specified interval, aligned with
a specified origin.
interval
is an expression of type interval
.
Timestamp
and origin
are expressions of type
timestamp with time zone
or timestamp without time zone
.
The return type matches the timestamp and origin types and will be either
timestamp with time zone
or timestamp without time zone
.
The return value marks the beginning of the bin into which the input timestamp is placed.
If you use an interval with a single unit like 1 second
or 1 minute
,
this function returns the same result as date_trunc.
If the interval is 1 week
, date_bin
only returns the same result as
date_trunc
if the origin is a Monday.
If at least one argument is NULL
, the return value is NULL
. The
interval cannot be zero. Negative intervals are allowed and are treated the
same as positive intervals. Intervals having month or year units are not
supported due to varying length of those units.
A timestamp can be binned to an interval of arbitrary length aligned with a custom origin.
Examples:
cr> SELECT date_bin('2 hours'::INTERVAL, ts,
... '2021-01-01T05:00:00Z'::TIMESTAMP) as bin,
... date_format('%y-%m-%d %H:%i',
... date_bin('2 hours'::INTERVAL, ts, '2021-01-01T05:00:00Z'::TIMESTAMP))
... formatted_bin
... FROM unnest(ARRAY[
... '2021-01-01T08:30:10Z',
... '2021-01-01T08:38:10Z',
... '2021-01-01T18:18:10Z',
... '2021-01-01T18:18:10Z'
... ]::TIMESTAMP[]) as tbl (ts);
+---------------+----------------+
| bin | formatted_bin |
+---------------+----------------+
| 1609484400000 | 21-01-01 07:00 |
| 1609484400000 | 21-01-01 07:00 |
| 1609520400000 | 21-01-01 17:00 |
| 1609520400000 | 21-01-01 17:00 |
+---------------+----------------+
SELECT 4 rows in set (... sec)
Tip
0 can be used as a shortcut for Unix zero as the origin:
cr> select date_bin('2 hours' :: INTERVAL,
... '2021-01-01T08:30:10Z' :: timestamp without time ZONE, 0) as bin;
+---------------+
| bin |
+---------------+
| 1609488000000 |
+---------------+
SELECT 1 row in set (... sec)
Please note, that implicit cast treats numbers as is, i.e. as a timestamp in that zone and if timestamp is in non-UTC zone you might want to set numeric origin to the same zone:
cr> select date_bin('4 hours' :: INTERVAL,
... '2020-01-01T09:00:00+0200'::timestamp with time zone,
... TIMEZONE('+02:00', 0)) as bin;
+---------------+
| bin |
+---------------+
| 1577858400000 |
+---------------+
SELECT 1 row in set (... sec)
extract(field from source)
¶
extract
is a special expression that translates
to a function which retrieves subcolumns such as day, hour or minute from a
timestamp.
The return type depends on the used field
.
Example:
cr> select extract(day from '2014-08-23') AS day;
+-----+
| day |
+-----+
| 23 |
+-----+
SELECT 1 row in set (... sec)
Synopsis:
EXTRACT( field FROM source )
field
An identifier or string literal which identifies the part of the timestamp that should be extracted.
source
An expression that resolves to a timestamp data type with or without timezone or is castable to timestamp data types. In the case the expression has a different return type but is known to be castable to timestamp an implicit cast will be attempted.
The following fields are supported:
CENTURY
- Return type:
integer
century of eraReturns the ISO representation which is a straight split of the date.
Year 2000 century 20 and year 2001 is also century 20. This is different to the GregorianJulian (GJ) calendar system where 2001 would be century 21.
YEAR
- Return type:
integer
the year field QUARTER
- Return type:
integer
the quarter of the year (1 - 4) MONTH
- Return type:
integer
the month of the year WEEK
- Return type:
integer
the week of the year DAY
- Return type:
integer
the day of the month DAY_OF_MONTH
- Return type:
integer
same asday
DAY_OF_WEEK
- Return type:
integer
day of the week. Starting with Monday (1) to Sunday (7) DOW
- Return type:
integer
same asday_of_week
DAY_OF_YEAR
- Return type:
integer
the day of the year (1 - 365 / 366) DOY
- Return type:
integer
same asday_of_year
HOUR
- Return type:
integer
the hour field MINUTE
- Return type:
integer
the minute field SECOND
- Return type:
integer
the second field EPOCH
- Return type:
double precision
The number of seconds since Jan 1, 1970.Can be negative if earlier than Jan 1, 1970.
CURRENT_TIME
¶
The CURRENT_TIME
expression returns the time in
microseconds since midnight UTC at the time the SQL statement was
handled. Clock time is looked up at most once within the scope of a single
query, to ensure that multiple occurrences of CURRENT_TIME
evaluate to the same value.
Synopsis:
CURRENT_TIME [ ( precision ) ]
precision
Must be a positive integer between 0 and 6. The default value is 6. It determines the number of fractional seconds to output. A value of 0 means the time will have second precision, no fractional seconds (microseconds) are given.
Note
No guarantee is provided about the accuracy of the underlying clock, results may be limited to millisecond precision, depending on the system.
CURRENT_TIMESTAMP
¶
The CURRENT_TIMESTAMP
expression returns the timestamp in milliseconds
since midnight UTC at the time the SQL statement was handled. Therefore, the
same timestamp value is returned for every invocation of a single statement.
Synopsis:
CURRENT_TIMESTAMP [ ( precision ) ]
precision
Must be a positive integer between
0
and3
. The default value is3
. This value determines the number of fractional seconds to output. A value of0
means the timestamp will have second precision, no fractional seconds (milliseconds) are given.
Tip
To get an offset value of CURRENT_TIMESTAMP
(e.g., this same time one
day ago), you can add or subtract an interval,
like so:
CURRENT_TIMESTAMP - '1 day'::interval
Note
If the CURRENT_TIMESTAMP
function is used in
Generated columns it behaves slightly different in UPDATE
operations. In such a case the actual timestamp of each row update is
returned.
CURDATE()
¶
The CURDATE()
scalar function is an alias of the CURRENT_DATE
expression.
Synopsis:
CURDATE()
CURRENT_DATE
¶
The CURRENT_DATE
expression returns the date in UTC timezone at the time
the SQL statement was handled.
Clock time is looked up at most once within the scope of a single query, to
ensure that multiple occurrences of CURRENT_DATE
evaluate to the same
value.
Synopsis:
CURRENT_DATE
now()
¶
Returns the current date and time in UTC.
This is the same as current_timestamp
Returns: timestamp with time zone
Synopsis:
now()
date_format([format_string, [timezone,]] timestamp)
¶
The date_format
function formats a timestamp as string according to the
(optional) format string.
Returns: text
Synopsis:
DATE_FORMAT( [ format_string, [ timezone, ] ] timestamp )
The only mandatory argument is the timestamp
value to format. It can be any
expression that is safely convertible to timestamp
data type with or without timezone.
The syntax for the format_string
is 100% compatible to the syntax of the
MySQL date_format function. For reference, the format is listed in detail
below:
Format Specifier |
Description |
---|---|
|
Abbreviated weekday name (Sun..Sat) |
|
Abbreviated month name (Jan..Dec) |
|
Month in year, numeric (0..12) |
|
Day of month as ordinal number (1st, 2nd, … 24th) |
|
Day of month, padded to 2 digits (00..31) |
|
Day of month (0..31) |
|
Microseconds, padded to 6 digits (000000..999999) |
|
Hour in 24-hour clock, padded to 2 digits (00..23) |
|
Hour in 12-hour clock, padded to 2 digits (01..12) |
|
Hour in 12-hour clock, padded to 2 digits (01..12) |
|
Minutes, numeric (00..59) |
|
Day of year, padded to 3 digits (001..366) |
|
Hour in 24-hour clock (0..23) |
|
Hour in 12-hour clock (1..12) |
|
Month name (January..December) |
|
Month in year, numeric, padded to 2 digits (00..12) |
|
AM or PM |
|
Time, 12-hour ( |
|
Seconds, padded to 2 digits (00..59) |
|
Seconds, padded to 2 digits (00..59) |
|
Time, 24-hour ( |
|
Week number, Sunday as first day of the week, first week of the year (01) is the one starting in this year, week 00 starts in last year (00..53) |
|
Week number, Monday as first day of the week, first week of the year (01) is the one with at least 4 days in this year (00..53) |
|
Week number, Sunday as first day of the week, first week of the year (01) is the one starting in this year, uses the week number of the last year, if the week started in last year (01..53) |
|
Week number, Monday as first day of the week, first week of the year (01) is the one with at least 4 days in this year, uses the week number of the last year, if the week started in last year (01..53) |
|
Weekday name (Sunday..Saturday) |
|
Day of the week (0=Sunday..6=Saturday) |
|
Week year, Sunday as first day of the week, numeric, four digits; used with %V |
|
Week year, Monday as first day of the week, numeric, four digits; used with %v |
|
Year, numeric, four digits |
|
Year, numeric, two digits |
|
A literal ‘%’ character |
|
x, for any ‘x’ not listed above |
If no format_string
is given the default format will be used:
%Y-%m-%dT%H:%i:%s.%fZ
cr> select date_format('1970-01-01') as epoque;
+-----------------------------+
| epoque |
+-----------------------------+
| 1970-01-01T00:00:00.000000Z |
+-----------------------------+
SELECT 1 row in set (... sec)
Valid values for timezone
are either the name of a time zone (for example
‘Europe/Vienna’) or the UTC offset of a time zone (for example ‘+01:00’). To
get a complete overview of all possible values take a look at the available
time zones supported by Joda-Time.
The timezone
will be UTC
if not provided:
cr> select date_format('%W the %D of %M %Y %H:%i %p', 0) as epoque;
+-------------------------------------------+
| epoque |
+-------------------------------------------+
| Thursday the 1st of January 1970 00:00 AM |
+-------------------------------------------+
SELECT 1 row in set (... sec)
cr> select date_format('%Y/%m/%d %H:%i', 'EST', 0) as est_epoque;
+------------------+
| est_epoque |
+------------------+
| 1969/12/31 19:00 |
+------------------+
SELECT 1 row in set (... sec)
timezone(timezone, timestamp)
¶
The timezone scalar function converts values of timestamp
without time zone
to/from timestamp with time zone.
Synopsis:
TIMEZONE(timezone, timestamp)
It has two variants depending on the type of timestamp
:
Type of timestamp |
Return Type |
Description |
---|---|---|
timestamp without time zone OR bigint |
timestamp with time zone |
Treat given timestamp without time zone as located in the specified timezone |
timestamp with time zone |
timestamp without time zone |
Convert given timestamp with time zone to the new timezone with no time zone designation |
cr> select
... 257504400000 as no_tz,
... date_format(
... '%Y-%m-%d %h:%i', 257504400000
... ) as no_tz_str,
... timezone(
... 'Europe/Madrid', 257504400000
... ) as in_madrid,
... date_format(
... '%Y-%m-%d %h:%i',
... timezone(
... 'Europe/Madrid', 257504400000
... )
... ) as in_madrid_str;
+--------------+------------------+--------------+------------------+
| no_tz | no_tz_str | in_madrid | in_madrid_str |
+--------------+------------------+--------------+------------------+
| 257504400000 | 1978-02-28 09:00 | 257500800000 | 1978-02-28 08:00 |
+--------------+------------------+--------------+------------------+
SELECT 1 row in set (... sec)
cr> select
... timezone(
... 'Europe/Madrid',
... '1978-02-28T10:00:00+01:00'::timestamp with time zone
... ) as epoque,
... date_format(
... '%Y-%m-%d %h:%i',
... timezone(
... 'Europe/Madrid',
... '1978-02-28T10:00:00+01:00'::timestamp with time zone
... )
... ) as epoque_str;
+--------------+------------------+
| epoque | epoque_str |
+--------------+------------------+
| 257508000000 | 1978-02-28 10:00 |
+--------------+------------------+
SELECT 1 row in set (... sec)
cr> select
... timezone(
... 'Europe/Madrid',
... '1978-02-28T10:00:00+01:00'::timestamp without time zone
... ) as epoque,
... date_format(
... '%Y-%m-%d %h:%i',
... timezone(
... 'Europe/Madrid',
... '1978-02-28T10:00:00+01:00'::timestamp without time zone
... )
... ) as epoque_str;
+--------------+------------------+
| epoque | epoque_str |
+--------------+------------------+
| 257504400000 | 1978-02-28 09:00 |
+--------------+------------------+
SELECT 1 row in set (... sec)
to_char(expression, format_string)
¶
The to_char
function converts a timestamp
or interval
value to a
string, based on a given format string.
Returns: text
Synopsis:
TO_CHAR( expression, format_string )
Here, expression
can be any value with the type of timestamp
(with or
without a timezone) or interval
.
The syntax for the format_string
differs based the type of the
expression. For timestamp
expressions, the
format_string
is a template string containing any of the following symbols:
Pattern |
Description |
---|---|
|
Hour of day (01-12) |
|
Hour of day (00-23) |
|
Minute (00-59) |
|
Second (00-59) |
|
Millisecond (000-999) |
|
Microsecond (000000-999999) |
|
Tenth of second (0-9) |
|
Hundredth of second (00-99) |
|
Millisecond (000-999) |
|
Tenth of millisecond (0000-9999) |
|
Hundredth of millisecond (00000-99999) |
|
Microsecond (000000-999999) |
|
Seconds past midnight (0-86399) |
|
Meridiem indicator |
|
Meridiem indicator (with periods) |
|
4 digit year with comma |
|
4 digit year |
|
Last 3 digits of year |
|
Last 2 digits of year |
|
Last digit of year |
|
4 digit ISO-8601 week-numbering year |
|
Last 3 digits of ISO-8601 week-numbering year |
|
Last 2 digits of ISO-8601 week-numbering year |
|
Last digit of ISO-8601 week-numbering year |
|
Era indicator |
|
Era indicator with periods |
|
Full month name (uppercase, capitalized, lowercase) padded to 9 characters |
|
Short month name (uppercase, capitalized, lowercase) padded to 9 characters |
|
Month number (01-12) |
|
Full day name (uppercase, capitalized, lowercase) padded to 9 characters |
|
Short, 3 character day name (uppercase, capitalized, lowercase) |
|
Day of year (001-366) |
|
Day of ISO-8601 week-numbering year, where the first Monday of the first ISO week is day 1 (001-371) |
|
Day of month (01-31) |
|
Day of the week, from Sunday (1) to Saturday (7) |
|
ISO-8601 day of the week, from Monday (1) to Sunday (7) |
|
Week of month (1-5) |
|
Week number of year (1-53) |
|
Week number of ISO-8601 week-numbering year (01-53) |
|
Century |
|
Julian Day |
|
Quarter |
|
Month in Roman numerals (uppercase, lowercase) |
|
Time-zone abbreviation (uppercase, lowercase) |
|
Time-zone hours |
|
Time-zone minutes |
|
Time-zone offset from UTC |
Example:
cr> select
... to_char(
... timestamp '1970-01-01T17:31:12',
... 'Day, Month DD - HH12:MI AM YYYY AD'
... ) as ts;
+-----------------------------------------+
| ts |
+-----------------------------------------+
| Thursday, January 01 - 05:31 PM 1970 AD |
+-----------------------------------------+
SELECT 1 row in set (... sec)
For interval
expressions, the formatting string accepts the same tokens as
timestamp
expressions. The function then uses the timestamp of the
specified interval added to the timestamp of 0000/01/01 00:00:00
:
cr> select
... to_char(
... interval '1 year 3 weeks 200 minutes',
... 'YYYY MM DD HH12:MI:SS'
... ) as interval;
+---------------------+
| interval |
+---------------------+
| 0001 01 22 03:20:00 |
+---------------------+
SELECT 1 row in set (... sec)
age([timestamp,] timestamp)
¶
Returns: interval between 2 timestamps. Second argument
is subtracted from the first one. If at least one argument is NULL
, the
return value is NULL
. If only one timestamp is given, the return value is
interval between current_date (at midnight) and the given timestamp.
Example:
cr> select pg_catalog.age('2021-10-21'::timestamp, '2021-10-20'::timestamp)
... as age;
+----------------+
| age |
+----------------+
| 1 day 00:00:00 |
+----------------+
SELECT 1 row in set (... sec)
cr> select pg_catalog.age(date_trunc('day', CURRENT_DATE)) as age;
+----------+
| age |
+----------+
| 00:00:00 |
+----------+
SELECT 1 row in set (... sec)
Geo functions¶
distance(geo_point1, geo_point2)
¶
Returns: double precision
The distance
function can be used to calculate the distance between two
points on earth. It uses the Haversine formula which gives great-circle
distances between 2 points on a sphere based on their latitude and longitude.
The return value is the distance in meters.
Below is an example of the distance function where both points are specified using WKT. See Geographic types for more information on the implicit type casting of geo points:
cr> select distance('POINT (10 20)', 'POINT (11 21)') AS col;
+-------------------+
| col |
+-------------------+
| 152354.3209044634 |
+-------------------+
SELECT 1 row in set (... sec)
This scalar function can always be used in both the WHERE
and ORDER BY
clauses. With the limitation that one of the arguments must be a literal and
the other argument must be a column reference.
Note
The algorithm of the calculation which is used when the distance function is used as part of the result column list has a different precision than what is stored inside the index which is utilized if the distance function is part of a WHERE clause.
For example, if select distance(...)
returns 0.0, an equality check
with where distance(...) = 0
might not yield anything at all due to the
precision difference.
within(shape1, shape2)
¶
Returns: boolean
The within
function returns true if shape1
is within shape2
. If
that is not the case false is returned.
shape1
can either be a geo_shape
or a geo_point
. shape2
must be
a geo_shape
.
Below is an example of the within
function which makes use of the implicit
type casting from strings in WKT representation to geo point and geo shapes:
cr> select within(
... 'POINT (10 10)',
... 'POLYGON ((5 5, 10 5, 10 10, 5 10, 5 5))'
... ) AS is_within;
+-----------+
| is_within |
+-----------+
| TRUE |
+-----------+
SELECT 1 row in set (... sec)
This function can always be used within the WHERE
clause.
intersects(geo_shape, geo_shape)
¶
Returns: boolean
The intersects
function returns true if both argument shapes share some
points or area, they overlap. This also includes two shapes where one lies
within the other.
If false
is returned, both shapes are considered disjoint.
Example:
cr> select
... intersects(
... {type='Polygon', coordinates=[
... [[13.4252, 52.7096],[13.9416, 52.0997],
... [12.7221, 52.1334],[13.4252, 52.7096]]]},
... 'LINESTRING(13.9636 52.6763, 13.2275 51.9578,
... 12.9199 52.5830, 11.9970 52.6830)'
... ) as intersects,
... intersects(
... {type='Polygon', coordinates=[
... [[13.4252, 52.7096],[13.9416, 52.0997],
... [12.7221, 52.1334],[13.4252, 52.7096]]]},
... 'LINESTRING (11.0742 49.4538, 11.5686 48.1367)'
... ) as disjoint;
+------------+----------+
| intersects | disjoint |
+------------+----------+
| TRUE | FALSE |
+------------+----------+
SELECT 1 row in set (... sec)
Due to a limitation on the Geometric shapes datatype this function cannot be used in the ORDER BY clause.
latitude(geo_point)
and longitude(geo_point)
¶
Returns: double precision
The latitude
and longitude
function return the coordinates of latitude
or longitude of a point, or NULL
if not available. The input must be a
column of type geo_point
, a valid WKT string or a double precision
array. See Geographic types for more information on the implicit type
casting of geo points.
Example:
cr> select
... mountain,
... height,
... longitude(coordinates) as "lon",
... latitude(coordinates) as "lat"
... from sys.summits
... order by height desc limit 1;
+------------+--------+---------+---------+
| mountain | height | lon | lat |
+------------+--------+---------+---------+
| Mont Blanc | 4808 | 6.86444 | 45.8325 |
+------------+--------+---------+---------+
SELECT 1 row in set (... sec)
Below is an example of the latitude/longitude functions which make use of the implicit type casting from strings to geo point:
cr> select
... latitude('POINT (10 20)') AS lat,
... longitude([10.0, 20.0]) AS long;
+------+------+
| lat | long |
+------+------+
| 20.0 | 10.0 |
+------+------+
SELECT 1 row in set (... sec)
geohash(geo_point)
¶
Returns: text
Returns a GeoHash representation
based on full precision (12 characters) of the input point, or NULL
if not
available. The input has to be a column of type geo_point
, a valid WKT
string or a double precision
array. See Geographic types for more
information of the implicit type casting of geo points.
Example:
cr> select
... mountain,
... height,
... geohash(coordinates) as "geohash"
... from sys.summits
... order by height desc limit 1;
+------------+--------+--------------+
| mountain | height | geohash |
+------------+--------+--------------+
| Mont Blanc | 4808 | u0huspw99j1r |
+------------+--------+--------------+
SELECT 1 row in set (... sec)
area(geo_shape)
¶
Returns: double precision
The area
function calculates the area of the input shape in
square-degrees. The calculation will use geospatial awareness (AKA geodetic)
instead of Euclidean geometry. The input has to be a column of type
Geometric shapes, a valid WKT string or GeoJSON.
See Geometric shapes for more information.
Below you can find an example.
Example:
cr> select
... round(area('POLYGON ((5 5, 10 5, 10 10, 5 10, 5 5))')) as "area";
+------+
| area |
+------+
| 25 |
+------+
SELECT 1 row in set (... sec)
Mathematical functions¶
All mathematical functions can be used within WHERE
and ORDER BY
clauses.
abs(number)
¶
Returns the absolute value of the given number in the datatype of the given number:
cr> select abs(214748.0998) AS a, abs(0) AS b, abs(-214748) AS c;
+-------------+---+--------+
| a | b | c |
+-------------+---+--------+
| 214748.0998 | 0 | 214748 |
+-------------+---+--------+
SELECT 1 row in set (... sec)
ceil(number)
¶
Returns the smallest integer or long value that is not less than the argument.
Returns: bigint
or integer
Return value will be of type integer
if the input value is an integer or
float. If the input value is of type bigint
or double precision
the
return value will be of type bigint
:
cr> select ceil(29.9) AS col;
+-----+
| col |
+-----+
| 30 |
+-----+
SELECT 1 row in set (... sec)
ceiling(number)
¶
This is an alias for ceil.
degrees(double precision)
¶
Convert the given radians
value to degrees
.
Returns: double precision
cr> select degrees(0.5) AS degrees;
+-------------------+
| degrees |
+-------------------+
| 28.64788975654116 |
+-------------------+
SELECT 1 row in set (... sec)
exp(number)
¶
Returns Euler’s number e
raised to the power of the given numeric value.
The output will be cast to the given input type and thus may loose precision.
Returns: Same as input type.
cr> select exp(1.0) AS exp;
+-------------------+
| exp |
+-------------------+
| 2.718281828459045 |
+-------------------+
SELECT 1 row in set (... sec)
floor(number)
¶
Returns the largest integer or long value that is not greater than the argument.
Returns: bigint
or integer
Return value will be an integer if the input value is an integer or a float. If
the input value is of type bigint
or double precision
the return value
will be of type bigint
.
See below for an example:
cr> select floor(29.9) AS floor;
+-------+
| floor |
+-------+
| 29 |
+-------+
SELECT 1 row in set (... sec)
ln(number)
¶
Returns the natural logarithm of given number
.
Returns: double precision
See below for an example:
cr> SELECT ln(1) AS ln;
+-----+
| ln |
+-----+
| 0.0 |
+-----+
SELECT 1 row in set (... sec)
Note
An error is returned for arguments which lead to undefined or illegal
results. E.g. ln(0) results in minus infinity
, and therefore, an error
is returned.
log(x : number, b : number)
¶
Returns the logarithm of given x
to base b
.
Returns: double precision
See below for an example, which essentially is the same as above:
cr> SELECT log(100, 10) AS log;
+-----+
| log |
+-----+
| 2.0 |
+-----+
SELECT 1 row in set (... sec)
The second argument (b
) is optional. If not present, base 10 is used:
cr> SELECT log(100) AS log;
+-----+
| log |
+-----+
| 2.0 |
+-----+
SELECT 1 row in set (... sec)
Note
An error is returned for arguments which lead to undefined or illegal
results. E.g. log(0) results in minus infinity
, and therefore, an error
is returned.
The same is true for arguments which lead to a division by zero
, as,
e.g., log(10, 1) does.
modulus(y, x)
¶
Returns the remainder of y/x
.
Returns: Same as argument types.
cr> select modulus(5, 4) AS mod;
+-----+
| mod |
+-----+
| 1 |
+-----+
SELECT 1 row in set (... sec)
mod(y, x)
¶
This is an alias for modulus.
power(a: number, b: number)
¶
Returns the given argument a
raised to the power of argument b
.
Returns: double precision
The return type of the power function is always double precision
, even when
both the inputs are integral types, in order to be consistent across positive
and negative exponents (which will yield decimal types).
See below for an example:
cr> SELECT power(2,3) AS pow;
+-----+
| pow |
+-----+
| 8.0 |
+-----+
SELECT 1 row in set (... sec)
radians(double precision)
¶
Convert the given degrees
value to radians
.
Returns: double precision
cr> select radians(45.0) AS radians;
+--------------------+
| radians |
+--------------------+
| 0.7853981633974483 |
+--------------------+
SELECT 1 row in set (... sec)
random()
¶
The random
function returns a random value in the range 0.0 <= X < 1.0.
Returns: double precision
Note
Every call to random
will yield a new random number.
gen_random_text_uuid()
¶
Returns a random time based UUID as text
. The returned ID is similar to
flake IDs and well suited for use as primary key value.
Note that the ID is opaque (i.e., not to be considered meaningful in any way) and the implementation is free to change.
round(number)
¶
If the input is of type double precision
or bigint
the result is the
closest bigint
to the argument, with ties rounding up.
If the input is of type real
or integer
the result is the closest
integer to the argument, with ties rounding up.
Returns: bigint
or integer
See below for an example:
cr> select round(42.2) AS round;
+-------+
| round |
+-------+
| 42 |
+-------+
SELECT 1 row in set (... sec)
trunc(number[, precision])
¶
Returns number
truncated to the specified precision
(decimal places).
When precision
is not specified, the result’s type is an integer
, or
bigint
. When it is specified, the result’s type is double precision
.
Notice that trunc(number)
and trunc(number, 0)
return different result
types.
See below for examples:
cr> select trunc(29.999999, 3) AS trunc;
+--------+
| trunc |
+--------+
| 29.999 |
+--------+
SELECT 1 row in set (... sec)
cr> select trunc(29.999999) AS trunc;
+-------+
| trunc |
+-------+
| 29 |
+-------+
SELECT 1 row in set (... sec)
sqrt(number)
¶
Returns the square root of the argument.
Returns: double precision
See below for an example:
cr> select sqrt(25.0) AS sqrt;
+------+
| sqrt |
+------+
| 5.0 |
+------+
SELECT 1 row in set (... sec)
sin(number)
¶
Returns the sine of the argument.
Returns: double precision
See below for an example:
cr> SELECT sin(1) AS sin;
+--------------------+
| sin |
+--------------------+
| 0.8414709848078965 |
+--------------------+
SELECT 1 row in set (... sec)
asin(number)
¶
Returns the arcsine of the argument.
Returns: double precision
See below for an example:
cr> SELECT asin(1) AS asin;
+--------------------+
| asin |
+--------------------+
| 1.5707963267948966 |
+--------------------+
SELECT 1 row in set (... sec)
cos(number)
¶
Returns the cosine of the argument.
Returns: double precision
See below for an example:
cr> SELECT cos(1) AS cos;
+--------------------+
| cos |
+--------------------+
| 0.5403023058681398 |
+--------------------+
SELECT 1 row in set (... sec)
acos(number)
¶
Returns the arccosine of the argument.
Returns: double precision
See below for an example:
cr> SELECT acos(-1) AS acos;
+-------------------+
| acos |
+-------------------+
| 3.141592653589793 |
+-------------------+
SELECT 1 row in set (... sec)
tan(number)
¶
Returns the tangent of the argument.
Returns: double precision
See below for an example:
cr> SELECT tan(1) AS tan;
+--------------------+
| tan |
+--------------------+
| 1.5574077246549023 |
+--------------------+
SELECT 1 row in set (... sec)
cot(number)
¶
Returns the cotangent of the argument that represents the angle expressed in
radians. The range of the argument is all real numbers. The cotangent of zero
is undefined and returns Infinity
.
Returns: double precision
See below for an example:
cr> select cot(1) AS cot;
+--------------------+
| cot |
+--------------------+
| 0.6420926159343306 |
+--------------------+
SELECT 1 row in set (... sec)
atan(number)
¶
Returns the arctangent of the argument.
Returns: double precision
See below for an example:
cr> SELECT atan(1) AS atan;
+--------------------+
| atan |
+--------------------+
| 0.7853981633974483 |
+--------------------+
SELECT 1 row in set (... sec)
atan2(y: number, x: number)
¶
Returns the arctangent of y/x
.
Returns: double precision
cr> SELECT atan2(2, 1) AS atan2;
+--------------------+
| atan2 |
+--------------------+
| 1.1071487177940904 |
+--------------------+
SELECT 1 row in set (... sec)
pi()
¶
Returns the π constant.
Returns: double precision
cr> SELECT pi() AS pi;
+-------------------+
| pi |
+-------------------+
| 3.141592653589793 |
+-------------------+
SELECT 1 row in set (... sec)
Regular expression functions¶
The regular expression functions in CrateDB use Java Regular Expressions.
See the API documentation for more details.
Note
Be aware that, in contrast to the functions, the regular expression operator uses Lucene Regular Expressions.
regexp_replace(source, pattern, replacement [, flags])
¶
regexp_replace
can be used to replace every (or only the first) occurrence
of a subsequence matching pattern
in the source
string with the
replacement
string. If no subsequence in source
matches the regular
expression pattern
, source
is returned unchanged.
Returns: text
pattern
is a Java regular expression. For details on the regexp syntax, see
Java Regular Expressions.
The replacement
string may contain expressions like $N
where N
is a
digit between 0 and 9. It references the nth matched group of pattern
and the matching subsequence of that group will be inserted in the returned
string. The expression $0
will insert the whole matching source
.
By default, only the first occurrence of a subsequence matching pattern
will be replaced. If all occurrences shall be replaced use the g
flag.
Flags¶
regexp_replace
supports a number of flags as optional parameters. These
flags are given as a string containing any of the characters listed below.
Order does not matter.
Flag |
Description |
---|---|
|
enable case insensitive matching |
|
enable unicode case folding when used together with |
|
enable unicode support for character classes like |
|
make |
|
make |
|
permit whitespace and line comments starting with |
|
only |
|
replace all occurrences of a subsequence matching |
Examples¶
cr> select
... name,
... regexp_replace(
... name, '(\w+)\s(\w+)+', '$1 - $2'
... ) as replaced
... from locations
... order by name limit 5;
+---------------------+-----------------------+
| name | replaced |
+---------------------+-----------------------+
| | |
| Aldebaran | Aldebaran |
| Algol | Algol |
| Allosimanius Syneca | Allosimanius - Syneca |
| Alpha Centauri | Alpha - Centauri |
+---------------------+-----------------------+
SELECT 5 rows in set (... sec)
cr> select
... regexp_replace(
... 'alcatraz', '(foo)(bar)+', '$1baz'
... ) as replaced;
+----------+
| replaced |
+----------+
| alcatraz |
+----------+
SELECT 1 row in set (... sec)
cr> select
... name,
... regexp_replace(
... name, '([A-Z]\w+) .+', '$1', 'ig'
... ) as replaced
... from locations
... order by name limit 5;
+---------------------+--------------+
| name | replaced |
+---------------------+--------------+
| | |
| Aldebaran | Aldebaran |
| Algol | Algol |
| Allosimanius Syneca | Allosimanius |
| Alpha Centauri | Alpha |
+---------------------+--------------+
SELECT 5 rows in set (... sec)
Array functions¶
array_append(anyarray, value)
¶
The array_append
function adds the value at the end of the array
Returns: array
cr> select
... array_append([1,2,3], 4) AS array_append;
+--------------+
| array_append |
+--------------+
| [1, 2, 3, 4] |
+--------------+
SELECT 1 row in set (... sec)
array_cat(first_array, second_array)
¶
The array_cat
function concatenates two arrays into one array
Returns: array
cr> select
... array_cat([1,2,3],[3,4,5,6]) AS array_cat;
+-----------------------+
| array_cat |
+-----------------------+
| [1, 2, 3, 3, 4, 5, 6] |
+-----------------------+
SELECT 1 row in set (... sec)
You can also use the concat operator ||
with
arrays:
cr> select
... [1,2,3] || [4,5,6] || [7,8,9] AS arr;
+-----------------------------+
| arr |
+-----------------------------+
| [1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------+
SELECT 1 row in set (... sec)
array_unique(first_array, [ second_array])
¶
The array_unique
function merges two arrays into one array with unique
elements
Returns: array
cr> select
... array_unique(
... [1, 2, 3],
... [3, 4, 4]
... ) AS arr;
+--------------+
| arr |
+--------------+
| [1, 2, 3, 4] |
+--------------+
SELECT 1 row in set (... sec)
If the arrays have different types all elements will be cast to a common type based on the type precedence.
cr> select
... array_unique(
... [10, 20],
... [10.0, 20.3]
... ) AS arr;
+--------------------+
| arr |
+--------------------+
| [10.0, 20.0, 20.3] |
+--------------------+
SELECT 1 row in set (... sec)
array_difference(first_array, second_array)
¶
The array_difference
function removes elements from the first array that
are contained in the second array.
Returns: array
cr> select
... array_difference(
... [1,2,3,4,5,6,7,8,9,10],
... [2,3,6,9,15]
... ) AS arr;
+---------------------+
| arr |
+---------------------+
| [1, 4, 5, 7, 8, 10] |
+---------------------+
SELECT 1 row in set (... sec)
array(subquery)
¶
The array(subquery)
expression is an array
constructor function which operates on the result of the subquery
.
Returns: array
See also
array_upper(anyarray, dimension)
¶
The array_upper
function returns the number of elements in the requested
array dimension (the upper bound of the dimension). CrateDB allows mixing
arrays with different sizes on the same dimension. Returns NULL
if array
argument is NULL
or if dimension <= 0 or if dimension is NULL
.
Returns: integer
cr> select array_upper([[1, 4], [3]], 1) AS size;
+------+
| size |
+------+
| 2 |
+------+
SELECT 1 row in set (... sec)
array_length(anyarray, dimension)
¶
The array_length
function returns the number of elements in the requested
array dimension.
Returns: integer
cr> select array_length([[1, 4], [3]], 1) AS len;
+-----+
| len |
+-----+
| 2 |
+-----+
SELECT 1 row in set (... sec)
array_lower(anyarray, dimension)
¶
The array_lower
function returns the lower bound of the requested array
dimension (which is 1
if the dimension is valid and has at least one
element). Returns NULL
if array argument is NULL
or if dimension <= 0
or if dimension is NULL
.
Returns: integer
cr> select array_lower([[1, 4], [3]], 1) AS size;
+------+
| size |
+------+
| 1 |
+------+
SELECT 1 row in set (... sec)
If there is at least one empty array or NULL
on the requested dimension
return value is NULL
. Example:
cr> select array_lower([[1, 4], [3], []], 2) AS size;
+------+
| size |
+------+
| NULL |
+------+
SELECT 1 row in set (... sec)
array_slice(anyarray, from, to)
¶
The array_slice
function returns a slice of the given array using the given
lower and upper bound.
Returns: array
See also
cr> select array_slice(['a', 'b', 'c', 'd'], 2, 3) AS arr;
+------------+
| arr |
+------------+
| ["b", "c"] |
+------------+
SELECT 1 row in set (... sec)
Note
The first index value is 1
. The maximum array index is 2147483647
.
Both the from
and to
index values are inclusive.
Using an index greater than the array size results in an empty array.
array_to_string(anyarray, separator, [ null_string ])
¶
The array_to_string
function concatenates elements of the given array into
a single string using the separator
.
Returns: text
cr> select
... array_to_string(
... ['Arthur', 'Ford', 'Trillian'], ','
... ) AS str;
+----------------------+
| str |
+----------------------+
| Arthur,Ford,Trillian |
+----------------------+
SELECT 1 row in set (... sec)
If the separator
argument is NULL
, the result is NULL
:
cr> select
... array_to_string(
... ['Arthur', 'Ford', 'Trillian'], NULL
... ) AS str;
+------+
| str |
+------+
| NULL |
+------+
SELECT 1 row in set (... sec)
If null_string
is provided and is not NULL
, then NULL
elements of
the array are replaced by that string, otherwise they are omitted:
cr> select
... array_to_string(
... ['Arthur', NULL, 'Trillian'], ',', 'Ford'
... ) AS str;
+----------------------+
| str |
+----------------------+
| Arthur,Ford,Trillian |
+----------------------+
SELECT 1 row in set (... sec)
cr> select
... array_to_string(
... ['Arthur', NULL, 'Trillian'], ','
... ) AS str;
+-----------------+
| str |
+-----------------+
| Arthur,Trillian |
+-----------------+
SELECT 1 row in set (... sec)
cr> select
... array_to_string(
... ['Arthur', NULL, 'Trillian'], ',', NULL
... ) AS str;
+-----------------+
| str |
+-----------------+
| Arthur,Trillian |
+-----------------+
SELECT 1 row in set (... sec)
string_to_array(string, separator, [ null_string ])
¶
The string_to_array
splits a string into an array of text
elements
using a supplied separator and an optional null-string to set matching
substring elements to NULL.
Returns: array(text)
cr> select string_to_array('Arthur,Ford,Trillian', ',') AS arr;
+--------------------------------+
| arr |
+--------------------------------+
| ["Arthur", "Ford", "Trillian"] |
+--------------------------------+
SELECT 1 row in set (... sec)
cr> select string_to_array('Arthur,Ford,Trillian', ',', 'Ford') AS arr;
+------------------------------+
| arr |
+------------------------------+
| ["Arthur", null, "Trillian"] |
+------------------------------+
SELECT 1 row in set (... sec)
separator
¶
If the separator
argument is NULL, each character of the input string
becomes a separate element in the resulting array.
cr> select string_to_array('Ford', NULL) AS arr;
+----------------------+
| arr |
+----------------------+
| ["F", "o", "r", "d"] |
+----------------------+
SELECT 1 row in set (... sec)
If the separator is an empty string, then the entire input string is returned as a one-element array.
cr> select string_to_array('Arthur,Ford', '') AS arr;
+-----------------+
| arr |
+-----------------+
| ["Arthur,Ford"] |
+-----------------+
SELECT 1 row in set (... sec)
null_string
¶
If the null_string
argument is omitted or NULL, none of the substrings of
the input will be replaced by NULL.
array_min(array)
¶
The array_min
function returns the smallest element in array
. If
array
is NULL
or an empty array, the function returns NULL
. This
function supports arrays of any of the primitive types.
cr> SELECT array_min([3, 2, 1]) AS min;
+-----+
| min |
+-----+
| 1 |
+-----+
SELECT 1 row in set (... sec)
array_position(anycompatiblearray, anycompatible [, integer ] ) → integer
¶
The array_position
function returns the position of the first
occurrence of the second argument in the array
, or NULL
if it’s not
present. If the third argument is given, the search begins at that position.
The third argument is ignored if it’s null. If not within the array
range,
NULL
is returned. It is also possible to search for NULL
values.
cr> SELECT array_position([1,3,7,4], 7) as position;
+----------+
| position |
+----------+
| 3 |
+----------+
SELECT 1 row in set (... sec)
Begin the search from given position (optional).
cr> SELECT array_position([1,3,7,4], 7, 2) as position;
+----------+
| position |
+----------+
| 3 |
+----------+
SELECT 1 row in set (... sec)
Tip
When searching for the existence of an array
element, using the
ANY operator inside the WHERE
clause is much more efficient as it can utilize the index whereas
array_position
won’t even when used inside the WHERE
clause.
array_max(array)
¶
The array_max
function returns the largest element in array
. If
array
is NULL
or an empty array, the function returns NULL
. This
function supports arrays of any of the primitive types.
cr> SELECT array_max([1,2,3]) AS max;
+-----+
| max |
+-----+
| 3 |
+-----+
SELECT 1 row in set (... sec)
array_sum(array)
¶
Returns the sum of array elements that are not NULL
. If array
is
NULL
or an empty array, the function returns NULL
. This function
supports arrays of any numeric types.
For real
and double precison
arguments, the return type is equal to the
argument type. For char
, smallint
, integer
, and bigint
arguments, the return type changes to bigint
.
If any bigint
value exceeds range limits (-2^64 to 2^64-1), an
ArithmeticException
will be raised.
cr> SELECT array_sum([1,2,3]) AS sum;
+-----+
| sum |
+-----+
| 6 |
+-----+
SELECT 1 row in set (... sec)
The sum on the bigint array will result in an overflow in the following query:
cr> SELECT
... array_sum(
... [9223372036854775807, 9223372036854775807]
... ) as sum;
ArithmeticException[long overflow]
To address the overflow of the sum of the given array elements, we cast the array to the numeric data type:
cr> SELECT
... array_sum(
... [9223372036854775807, 9223372036854775807]::numeric[]
... ) as sum;
+----------------------+
| sum |
+----------------------+
| 18446744073709551614 |
+----------------------+
SELECT 1 row in set (... sec)
array_avg(array)
¶
Returns the average of all values in array
that are not NULL
If
array
is NULL
or an empty array, the function returns NULL
. This
function supports arrays of any numeric types.
For real
and double precison
arguments, the return type is equal to the
argument type. For char
, smallint
, integer
, and bigint
arguments, the return type is numeric
.
cr> SELECT array_avg([1,2,3]) AS avg;
+-----+
| avg |
+-----+
| 2 |
+-----+
SELECT 1 row in set (... sec)
Conditional functions and expressions¶
CASE WHEN ... THEN ... END
¶
The case
expression is a generic conditional
expression similar to if/else statements in other programming languages and can
be used wherever an expression is valid.
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END
Each condition expression must result in a boolean value. If the condition’s
result is true, the value of the result expression that follows the condition
will be the final result of the case
expression and the subsequent when
branches will not be processed. If the condition’s result is not true, any
subsequent when
clauses are examined in the same manner. If no when
condition yields true, the value of the case
expression is the result of
the else
clause. If the else
clause is omitted and no condition is
true, the result is null.
Example:
cr> select id,
... case when id = 0 then 'zero'
... when id % 2 = 0 then 'even'
... else 'odd'
... end as parity
... from case_example order by id;
+----+--------+
| id | parity |
+----+--------+
| 0 | zero |
| 1 | odd |
| 2 | even |
| 3 | odd |
+----+--------+
SELECT 4 rows in set (... sec)
As a variant, a case
expression can be written using the simple form:
CASE expression
WHEN value THEN result
[WHEN ...]
[ELSE result]
END
Example:
cr> select id,
... case id when 0 then 'zero'
... when 1 then 'one'
... else 'other'
... end as description
... from case_example order by id;
+----+-------------+
| id | description |
+----+-------------+
| 0 | zero |
| 1 | one |
| 2 | other |
| 3 | other |
+----+-------------+
SELECT 4 rows in set (... sec)
Note
All result expressions must be convertible to a single data type.
if(condition, result [, default])
¶
The if
function is a conditional function comparing to if statements of
most other programming languages. If the given condition expression evaluates to true
, the
result expression is evaluated and its value is returned. If the condition
evaluates to false
, the result expression is not evaluated and the
optional given default expression is evaluated instead and its value will be
returned. If the default argument is omitted, NULL
will be returned
instead.
cr> select
... id,
... if(id = 0, 'zero', 'other') as description
... from if_example
... order by id;
+----+-------------+
| id | description |
+----+-------------+
| 0 | zero |
| 1 | other |
| 2 | other |
| 3 | other |
+----+-------------+
SELECT 4 rows in set (... sec)
coalesce('first_arg', second_arg [, ... ])
¶
The coalesce
function takes one or more arguments of the same type and
returns the first non-null value of these. The result will be NULL only if all
the arguments evaluate to NULL.
Returns: same type as arguments
cr> select coalesce(clustered_by, 'nothing') AS clustered_by
... from information_schema.tables
... where table_name='nodes';
+--------------+
| clustered_by |
+--------------+
| nothing |
+--------------+
SELECT 1 row in set (... sec)
greatest('first_arg', second_arg[ , ... ])
¶
The greatest
function takes one or more arguments of the same type and will
return the largest value of these. NULL values in the arguments list are
ignored. The result will be NULL only if all the arguments evaluate to NULL.
Returns: same type as arguments
cr> select greatest(1, 2) AS greatest;
+----------+
| greatest |
+----------+
| 2 |
+----------+
SELECT 1 row in set (... sec)
least('first_arg', second_arg[ , ... ])
¶
The least
function takes one or more arguments of the same type and will
return the smallest value of these. NULL values in the arguments list are
ignored. The result will be NULL only if all the arguments evaluate to NULL.
Returns: same type as arguments
cr> select least(1, 2) AS least;
+-------+
| least |
+-------+
| 1 |
+-------+
SELECT 1 row in set (... sec)
nullif('first_arg', second_arg)
¶
The nullif
function compares two arguments of the same type and, if they
have the same value, returns NULL; otherwise returns the first argument.
Returns: same type as arguments
cr> select nullif(table_schema, 'sys') AS nullif
... from information_schema.tables
... where table_name='nodes';
+--------+
| nullif |
+--------+
| NULL |
+--------+
SELECT 1 row in set (... sec)
System information functions¶
CURRENT_SCHEMA
¶
The CURRENT_SCHEMA
system information function returns the name of the
current schema of the session. If no current schema is set, this function will
return the default schema, which is doc
.
Returns: text
The default schema can be set when using the JDBC client and HTTP clients such as CrateDB PDO.
Note
The CURRENT_SCHEMA
function has a special SQL syntax, meaning that it
must be called without trailing parenthesis (()
). However, CrateDB also
supports the optional parenthesis.
Synopsis:
CURRENT_SCHEMA [ ( ) ]
Example:
cr> SELECT CURRENT_SCHEMA;
+----------------+
| current_schema |
+----------------+
| doc |
+----------------+
SELECT 1 row in set (... sec)
CURRENT_SCHEMAS(boolean)
¶
The CURRENT_SCHEMAS()
system information function returns the current
stored schemas inside the search_path session
state, optionally including implicit schemas (e.g. pg_catalog
). If no
custom search_path is set, this function will
return the default search_path schemas.
Returns: array(text)
Synopsis:
CURRENT_SCHEMAS ( boolean )
Example:
cr> SELECT CURRENT_SCHEMAS(true) AS schemas;
+-----------------------+
| schemas |
+-----------------------+
| ["pg_catalog", "doc"] |
+-----------------------+
SELECT 1 row in set (... sec)
CURRENT_USER
¶
The CURRENT_USER
system information function returns the name of the
current connected user or crate
if the user management module is disabled.
Returns: text
Synopsis:
CURRENT_USER
Example:
cr> select current_user AS name;
+-------+
| name |
+-------+
| crate |
+-------+
SELECT 1 row in set (... sec)
USER
¶
Equivalent to CURRENT_USER.
Returns: text
Synopsis:
USER
Example:
cr> select user AS name;
+-------+
| name |
+-------+
| crate |
+-------+
SELECT 1 row in set (... sec)
SESSION_USER
¶
The SESSION_USER
system information function returns the name of the
current connected user or crate
if the user management module is disabled.
Returns: text
Synopsis:
SESSION_USER
Example:
cr> select session_user AS name;
+-------+
| name |
+-------+
| crate |
+-------+
SELECT 1 row in set (... sec)
Note
CrateDB doesn’t currently support the switching of execution context. This makes SESSION_USER functionally equivalent to CURRENT_USER. We provide it as it’s part of the SQL standard.
Additionally, the CURRENT_USER, SESSION_USER and USER functions
have a special SQL syntax, meaning that they must be called without
trailing parenthesis (()
).
pg_backend_pid()
¶
The pg_backend_pid()
system information function is implemented for
enhanced compatibility with PostgreSQL. CrateDB will always return -1
as
there isn’t a single process attached to one query. This is different to
PostgreSQL, where this represents the process ID of the server process attached
to the current session.
Returns: integer
Synopsis:
pg_backend_pid()
Example:
cr> select pg_backend_pid() AS pid;
+-----+
| pid |
+-----+
| -1 |
+-----+
SELECT 1 row in set (... sec)
pg_postmaster_start_time()
¶
Returns the server start time as timestamp with time zone
.
current_database()
¶
The current_database
function returns the name of the current database,
which in CrateDB will always be crate
:
cr> select current_database() AS db;
+-------+
| db |
+-------+
| crate |
+-------+
SELECT 1 row in set (... sec)
current_setting(text [,boolean])
¶
The current_setting
function returns the current value of a session
setting.
Returns: text
Synopsis:
current_setting(setting_name [, missing_ok])
If no setting exists for setting_name
, current_setting throws an error,
unless missing_ok
argument is provided and is true.
Examples:
cr> select current_setting('search_path') AS search_path;
+-----------------+
| search_path |
+-----------------+
| pg_catalog, doc |
+-----------------+
SELECT 1 row in set (... sec)
cr> select current_setting('foo');
SQLParseException[Unrecognised Setting: foo]
cr> select current_setting('foo', true) AS foo;
+------+
| foo |
+------+
| NULL |
+------+
SELECT 1 row in set (... sec)
pg_get_expr()
¶
The function pg_get_expr
is implemented to improve compatibility with
clients that use the PostgreSQL wire protocol. The function always returns
null
.
Synopsis:
pg_get_expr(expr text, relation_oid int)
Example:
cr> select pg_get_expr('literal', 1) AS col;
+------+
| col |
+------+
| NULL |
+------+
SELECT 1 row in set (... sec)
pg_encoding_to_char()
¶
The function pg_encoding_to_char
converts an PostgreSQL encoding’s internal
identifier to a human-readable name.
Returns: text
Synopsis:
pg_encoding_to_char(encoding int)
Example:
cr> select pg_encoding_to_char(6) AS encoding;
+----------+
| encoding |
+----------+
| UTF8 |
+----------+
SELECT 1 row in set (... sec)
pg_get_userbyid()
¶
The function pg_get_userbyid
is implemented to improve compatibility with
clients that use the PostgreSQL wire protocol. The function always returns the
default CrateDB user for non-null arguments, otherwise, null
is returned.
Returns: text
Synopsis:
pg_get_userbyid(id integer)
Example:
cr> select pg_get_userbyid(1) AS name;
+-------+
| name |
+-------+
| crate |
+-------+
SELECT 1 row in set (... sec)
pg_typeof()
¶
The function pg_typeof
returns the text representation of the value’s data
type passed to it.
Returns: text
Synopsis:
pg_typeof(expression)
Example:
cr> select pg_typeof([1, 2, 3]) as typeof;
+---------------+
| typeof |
+---------------+
| integer_array |
+---------------+
SELECT 1 row in set (... sec)
pg_function_is_visible()
¶
The function pg_function_is_visible
returns true for OIDs that refer to a
system or a user defined function.
Returns: boolean
Synopsis:
pg_function_is_visible(OID)
Example:
cr> select pg_function_is_visible(-919555782) as pg_function_is_visible;
+------------------------+
| pg_function_is_visible |
+------------------------+
| TRUE |
+------------------------+
SELECT 1 row in set (... sec)
pg_get_function_result()
¶
The function pg_get_function_result
returns the text representation of the
return value’s data type of the function referred by the OID.
Returns: text
Synopsis:
pg_get_function_result(OID)
Example:
cr> select pg_get_function_result(-919555782) as _pg_get_function_result;
+-------------------------+
| _pg_get_function_result |
+-------------------------+
| time with time zone |
+-------------------------+
SELECT 1 row in set (... sec)
version()
¶
Returns the CrateDB version information.
Returns: text
Synopsis:
version()
Example:
cr> select version() AS version;
+---------...-+
| version |
+---------...-+
| CrateDB ... |
+---------...-+
SELECT 1 row in set (... sec)
obj_description(integer, text)
¶
This function exists mainly for compatibility with PostgreSQL. In PostgreSQL,
the function returns the comment for a database object. CrateDB doesn’t support
user defined comments for database objects, so it always returns null
.
Returns: text
Example:
cr> SELECT pg_catalog.obj_description(1, 'pg_type') AS comment;
+---------+
| comment |
+---------+
| NULL |
+---------+
SELECT 1 row in set (... sec)
format_type(integer, integer)
¶
Returns the type name of a type. The first argument is the OID
of the type.
The second argument is the type modifier. This function exits for PostgreSQL
compatibility and the type modifier is always ignored.
Returns: text
Example:
cr> SELECT pg_catalog.format_type(25, null) AS name;
+------+
| name |
+------+
| text |
+------+
SELECT 1 row in set (... sec)
If the given OID
is not know, ???
is returned:
cr> SELECT pg_catalog.format_type(3, null) AS name;
+------+
| name |
+------+
| ??? |
+------+
SELECT 1 row in set (... sec)
Special functions¶
ignore3vl(boolean)
¶
The ignore3vl
function operates on a boolean argument and eliminates the
3-valued logic on the whole tree of operators
beneath it. More specifically, FALSE
is evaluated
to FALSE
, TRUE
to TRUE
and NULL
to FALSE
.
Returns: boolean
Note
The main usage of the ignore3vl
function is in the WHERE
clause
when a NOT
operator is involved. Such filtering, with 3-valued
logic, cannot be translated to an optimized query in the internal storage
engine, and therefore can degrade performance. E.g.:
SELECT * FROM t
WHERE NOT 5 = ANY(t.int_array_col);
If we can ignore the 3-valued logic, we can write the query as:
SELECT * FROM t
WHERE NOT IGNORE3VL(5 = ANY(t.int_array_col));
which will yield better performance (in execution time) than before.
Caution
If there are NULL
values in the long_array_col
, in the case that
5 = ANY(t.long_array_col)
evaluates to NULL
, without the
ignore3vl
, it would be evaluated as NOT NULL
=> NULL
, resulting
to zero matched rows. With the IGNORE3VL
in place it will be evaluated
as NOT FALSE
=> TRUE
resulting to all rows matching the
filter. E.g:
cr> SELECT * FROM t
... WHERE NOT 5 = ANY(t.int_array_col);
+---------------+
| int_array_col |
+---------------+
+---------------+
SELECT 0 rows in set (... sec)
cr> SELECT * FROM t
... WHERE NOT IGNORE3VL(5 = ANY(t.int_array_col));
+-----------------+
| int_array_col |
+-----------------+
| [1, 2, 3, null] |
+-----------------+
SELECT 1 row in set (... sec)
Synopsis:
ignore3vl(boolean)
Example:
cr> SELECT
... ignore3vl(true) as v1,
... ignore3vl(false) as v2,
... ignore3vl(null) as v3;
+------+-------+-------+
| v1 | v2 | v3 |
+------+-------+-------+
| TRUE | FALSE | FALSE |
+------+-------+-------+
SELECT 1 row in set (... sec)