Scalar functions

Scalar functions are functions that return scalars.

Table of contents

• String functions

  • concat('first_arg', second_arg, [ parameter , ... ])

  • format('format_string', parameter, [ parameter , ... ])

  • substr('string', from, [ count ])

  • char_length('string')

  • length(text)

  • bit_length('string')

  • octet_length('string')

  • ascii(string)

  • chr(int)

  • lower('string')

  • upper('string')

  • initcap('string')

  • sha1('string')

  • md5('string')

  • replace(text, from, to)

  • translate(string, from, to)

  • trim({LEADING | TRAILING | BOTH} 'str_arg_1' FROM 'str_arg_2')

  • ltrim(text, [ trimmingText ])

  • rtrim(text, [ trimmingText ])

  • quote_ident(text)

  • left('string', len)

  • right('string', len)

  • lpad('string1', len[, 'string2'])

  • rpad('string1', len[, 'string2'])

  • encode(bytea, format)

  • decode(text, format)

  • repeat(text, integer)

  • split_part(text, text, integer)

• Date and time functions

  • date_trunc('interval', ['timezone',] timestamp)

  • extract(field from source)

    • Synopsis

  • CURRENT_TIME

  • CURRENT_TIMESTAMP

  • CURDATE()

  • CURRENT_DATE

  • now()

  • date_format([format_string, [timezone,]] timestamp)

    • Synopsis

    • Format

    • Timezone

  • timezone(timezone, timestamp)

    • Synopsis

  • to_char(expression, format_string)

    • Synopsis

    • Format

• Geo functions

  • distance(geo_point1, geo_point2)

  • within(shape1, shape2)

  • intersects(geo_shape, geo_shape)

  • latitude(geo_point) and longitude(geo_point)

  • geohash(geo_point)

• Mathematical functions

  • abs(number)

  • ceil(number)

  • ceiling(number)

  • degrees(double precision)

  • exp(number)

  • floor(number)

  • ln(number)

  • log(x : number, b : number)

  • modulus(y, x)

  • mod(y, x)

  • power(a: number, b: number)

  • radians(double precision)

  • random()

  • gen_random_text_uuid()

  • round(number)

  • trunc(number[, precision])

  • sqrt(number)

  • sin(number)

  • asin(number)

  • cos(number)

  • acos(number)

  • tan(number)

  • cot(number)

  • atan(number)

  • atan2(y: number, x: number)

  • pi()

• Regular expression functions

  • regexp_replace(source, pattern, replacement [, flags])

    • Flags

    • Examples

• Array functions

  • array_cat(first_array, second_array)

  • array_unique(first_array, [ second_array])

  • array_difference(first_array, second_array)

  • array(subquery)

  • array_upper(anyarray, dimension)

  • array_length(anyarray, dimension)

  • array_lower(anyarray, dimension)

  • string_to_array(string, separator, [ null_string ])

    • separator

    • null_string

• Conditional functions and expressions

  • CASE WHEN ... THEN ... END

  • if(condition, result [, default])

  • coalesce('first_arg', second_arg [, ... ])

  • greatest('first_arg', second_arg[ , ... ])

  • least('first_arg', second_arg[ , ... ])

  • nullif('first_arg', second_arg)

• System information functions

  • CURRENT_SCHEMA

  • CURRENT_SCHEMAS(boolean)

  • CURRENT_USER

  • USER

  • SESSION_USER

  • pg_backend_pid()

  • pg_postmaster_start_time()

  • current_database()

  • current_setting(text [,boolean])

  • pg_get_expr

  • pg_get_userbyid

  • pg_typeof

  • pg_function_is_visible

  • pg_get_function_result

  • version

  • obj_description(integer, text)

  • format_type(integer, integer)

• Special functions

  • ignore3vl(boolean)

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 len 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 is NULL.

• 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)

extract(field from source)

extract is a special expression that translates to a function which retrieves subfields such as day, hour or minute from a timestamp.

The return type depends on the used field.

Synopsis

EXTRACT( field FROM expression )

field

An identifier or string literal which identifies the part of the timestamp that should be extracted.

expression

An expression that resolves to a timestamp data type with or without timezone or is castable to timestamp data types.

cr> select extract(day from '2014-08-23') AS day;
+-----+
| day |
+-----+
|  23 |
+-----+
SELECT 1 row in set (... sec)

source must be an expression that returns a timestamp. In case the expression has a different return type but is known to be castable to timestamp an implicit cast will be attempted.

field is an identifier that selects which part of the timestamp to extract. The following fields are supported:

CENTURY

Return type: integer

century of era

Returns 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 as day

DAY_OF_WEEK

Return type: integer

day of the week. Starting with Monday (1) to Sunday (7)

DOW

Return type: integer

same as day_of_week

DAY_OF_YEAR

Return type: integer

the day of the year (1 - 365 / 366)

DOY

Return type: integer

same as day_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 epoch 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 ) ]

Here, precision must be a positive integer between 0 and 3. The default value is 3. This value determines the number of fractional seconds to output. A value of 0 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.

The return value of expressions CURRENT_TIMESTAMP and CURRENT_TIME depends on the system clock and the JVM implementation.

CURDATE()

The CURDATE() scalar function is an alias of the CURRENT_DATE expression. See CURRENT_DATE.

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.

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.

Format

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 1:

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)

Timezone

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:

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.000+01:00'::timestamp with time zone) as epoque,
... date_format('%Y-%m-%d %h:%i', timezone('Europe/Madrid',
... '1978-02-28T10:00:00.000+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.000+01:00'::timestamp without time zone) as epoque,
... date_format('%Y-%m-%d %h:%i', timezone('Europe/Madrid',
... '1978-02-28T10:00:00.000+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 )

expression can be any value with the type of timestamp (with or without a timezone) or interval.

Format

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:

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)

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 geo_point 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 geo_shape 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 geo_point 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 geo_point 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)

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.

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_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)

It can be used to append elements to array fields

cr> create table array_cat_example (list array(integer));
CREATE OK, 1 row affected (... sec)

cr> insert into array_cat_example (list) values ([1,2,3]);
INSERT OK, 1 row affected (... sec)

cr> update array_cat_example set list = array_cat(list, [4, 5, 6]);
UPDATE OK, 1 row affected (... sec)

cr> select * from array_cat_example;
+--------------------+
| list               |
+--------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------+
SELECT 1 row in set (... sec)

Note

Appending to arrays with array_cat in updates is handy, but unfortunately not isolated. We use optimistic concurrency control to ensure that your update operation used the latest state of the row. But only 3 retry attempts are made by fetching the newest version again and if they all fail, the query fails.

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)