Data types

INT

A 4-byte integer

Range

-2147483648 .. 2147483647

Aliases

INTEGER, SMALLINT, TINYINT, INT2, INT4. All these are 4-byte integer types.

Examples
SELECT CAST(12.3 AS INT);  -- returns {"INT32":12}
CREATE TABLE type_test(intvalue INT);

UNSIGNED INT

A 4-byte unsigned integer

Range

0 .. 4294967295

Aliases

UNSIGNED INTEGER, UNSIGNED SMALLINT, UNSIGNED TINYINT, UNSIGNED INT2, UNSIGNED INT4, UINT, UINT2, UINT4

Examples
SELECT CAST(12.3 AS UNSIGNED INT);
CREATE TABLE type_test(i UNSIGNED INT);
INSERT INTO type_test (i) VALUES (100);
SELECT *, TYPE(i) FROM type_test;
Result
{"UNSIGNED INT":12}
{"_id":1,"i":100,"type":"UNSIGNED INT"}

BIGINT

An 8-byte integer

Range

-9223372036854775807 .. 9223372036854775807

Aliases

INT8

Examples
SELECT CAST(12.3 AS BIGINT);  -- returns {"INT64":12}
CREATE TABLE type_test(bigvalue BIGINT);

UNSIGNED BIGINT

An 8-byte unsigned integer.

Range

0 .. 18446744073709551615

Aliases

UNSIGNED INT8, UINT8

Examples
CREATE TABLE type_test();
INSERT INTO type_test(x) VALUES(18446744073709551615);
SELECT x, TYPE(x) FROM type_test;
Result
{"x":18446744073709551615,"type":"UNSIGNED BIGINT"}

DOUBLE

Range

4.94065645841246544e-324 .. 1.79769313486231570e+308

-4.94065645841246544e-324 .. -1.79769313486231570e+308

Aliases

FLOAT8, FLOAT, FLOAT4

Examples
SELECT CAST(12.40282e+14 AS DOUBLE);
CREATE TABLE type_test(dvalue DOUBLE);

BOOL

Range

true or false

You do not need to quote these literals. It is possible to use uppercase, lowercase or mixed case literals.

Aliases

BOOLEAN

Usage notes

You cannot cast the DATETIME and BINARY types to BOOL.

Examples
SELECT CAST(12.40282e+14 as boolean);  -- returns {"BOOL":true}
SELECT CAST(TRUE as boolean);          -- returns {"BOOL":true}
SELECT CAST(FaLsE as boolean);         -- returns {"BOOL":false}
SELECT CAST(0 as boolean);             -- returns {"BOOL":false}
SELECT CAST('false' as boolean);       -- returns {"BOOL":false}
CREATE TABLE type_test(bvalue BOOL);

STRING

A variable-length character type.

Length

Unlimited

Aliases

BPCHAR, CHAR, VARCHAR, TEXT

Usage notes

You can use the VARCHAR(N) syntax, but this is a compatibility feature only. Using N does not restrict the string size.

Only strings enclosed in single quotes are treated as string literals. Double quotes are used to construct delimited identifiers and JSON fields/values.

The standard way to include an apostrophe (') in a string literal is to escape it with another apostrophe. In addition, Raijin also supports backslash (C-style) escaping, so the following queries are equivalent:

SELECT 'A string with an apostrophe '' inside';
SELECT 'A string with an apostrophe \' inside';
Backslash (C-style) escaping

Raijin accepts "escape" string constants/delimited identifiers, an extension to the SQL standard. There are two ways to specify the escape string/identifier:

  • If the BackslashEscaping configuration parameter is turned on, you can use backslash escapes inside regular strings and quoted identifiers.

  • Else, use the extended prefix syntax by writing the E character in either upper or lowercase just before the opening single or double quote, e.g., E’foo' or e"foo".

Within an escape string, the backslash character (\) defines the beginning of a C-like escape sequence representing a special byte value, as shown in the following table.

Table 1. Backslash escape sequences
Backslash escape sequence Interpretation

\b

Backspace

\f

Form feed

\n

Newline

\r

Carriage return

\t

Tab

\o, \oo, \ooo (o = 0 - 7)

Octal byte value

\xh, \xhh (h = 0 - 9, A - F)

Hexadecimal byte value

\uxxxx, \Uxxxxxxxx (x = 0 - 9, A - F)

16 or 32-bit hexadecimal Unicode character value

Any other character following a backslash is considered as a string literal. Thus, to include a backslash character, write two backslashes (\\). You can also escape a single quot by writing \' in addition to the standard ''; the same applies to double quotes in delimited identifiers, i.e., \" can be used instead of the standard "".

Raijn supports the 4-digit and the 8-digit forms of Unicode escape syntax to specify UTF-16 surrogate pairs to compose characters with code points larger than U+FFFF. However, the availability of the 8-digit form technically makes this unnecessary. Surrogate pairs are first combined into a single code point and then encoded in UTF-8.

The BackslashEscaping configuration parameter can be turned off. In that case, the backslash character (\) is treated as a regular symbol and not the beginning of an escape sequence. So, \' or \" will be treated as the end of a string literal or delimited identifier. To include a quote in a regular string or identifier, duplicate the single '' or double "" quote accordingly.

BackslashEscaping turned on
SELECT SUBSTRING ('foo \'bar\'', 5, 5); (1)
SELECT '\U0001F431'; (2)
SELECT '\uD83D\uDC31';
CREATE TABLE type_test(str STRING); (3)
SELECT * FROM "\164ype_\x74est"; (4)
1 Returns 'bar'
2 Returns a Unicode code point representation
3 Returns a Unicode surrogate pair representation
4 Executes SELECT * FROM type_test;
BackslashEscaping turned off
                       (1)
SELECT SUBSTRING ('foo \'<bar\'', 5, 5); (2)
SELECT '\U0001F431'; (3)
SELECT '\uD83D\uDC31'; (4)
CREATE TABLE type_test(str STRING);
SELECT * FROM "\164ype_\x74est"; (5)
1 This apostrophe (') will be treated as the end of the string
2 Error: syntax error at or near "bar"
3 Returns \U0001F431
4 Returns \uD83D\uDC31
5 Error: Table with name '\164ype_\x74est' does not exist

See also String functions

DATETIME

Range

Any datetime, starting from 1970-01-01 00:00:00 up to 3799-12-31 23:59:59.9999

Aliases

TIMESTAMP

Usage notes

Datetime values are accepted in the following formats (ISO Date, rfc3339):

1977-09-06 01:02:03
1977-09-06 01:02:03.004
1977-09-06T01:02:03.004Z
1977-09-06T01:02:03.004+02:00
2011-5-29 0:3:21
2011-5-29 0:3:21+02:00
2011-5-29 0:3:21.004
2011-5-29 0:3:21.004+02:00

Nanosecond resolution is supported, but values are rounded to microseconds:

CREATE TABLE tbl(event_time TIMESTAMP);
INSERT INTO tbl (event_time) VALUES ('2022-10-29 18:46:26.946000999');
SELECT * FROM tbl;

-- yields the following (concrete value depends on a local timezone):
{"_id":1,"event_time":"2022-10-30T02:46:26.946001+08:00"}

You cannot cast DATETIME to BOOL.

Examples
SELECT CAST ('1970-02-03 22:14:39' as DATETIME);
CREATE TABLE type_test(value DATETIME);

VARIANT

A Variant is a special data type that can contain any type of data.

In contrast to other Raijin data types, queries cannot explicitly create fields using the VARIANT data type. Instead, a field will show as VARIANT when data of different types is created without type declaration and inserted.

Usage notes

Only numeric values are counted when using VARIANT columns containing mixed data types in aggregation functions such as AVG, SUM, MIN, and MAX. Non-numeric values, such as STRING and DATETIME, are considered NULL and excluded.

To include non-numeric values in aggregate calculations, you must explicitly typecast the column to the expected numeric data type. Non-numeric values that cannot be converted to the specified data type will result in a NULL value and will not be included in the calculation.

Examples
CREATE TABLE type_test();
INSERT INTO type_test(x) VALUES (true), (false), (null), (5), (CAST(5 AS BIGINT)),
            (CAST(1.23 AS DOUBLE)), (CAST('1970-02-03 22:14:39Z' as DATETIME));
SELECT FIELDS FROM type_test;

CREATE TABLE sum_test();
INSERT INTO sum_test(x) VALUES (1), ('20');
SELECT SUM(x) from sum_test;
SELECT SUM(x::DOUBLE) from sum_test;
Result
{"field":"x","type":"VARIANT"}
{"sum":1}
{"sum":21}

BINARY

This data type is not currently implemented.

Aliases

VARBINARY

INTERVAL

This data type is not currently implemented.