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: -1.79769313486231570e+308 .. 1.79769313486231570e+308
Alias: FLOAT8
Examples

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

FLOAT

Range: -3.4028234663852e+38 .. 3.4028234663852e+38
Aliases: FLOAT4 and REAL
Examples

SELECT CAST(12.40282e+14 AS FLOAT);
CREATE TABLE type_test(fvalue FLOAT);

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 TIMESTAMP 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

TIMESTAMP

Range: Any timestamp, starting from 1970-01-01 00:00:00 up to 3799-12-31 23:59:59.9999
Aliases: DATETIME
Usage notes: Timestamp 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 TIMESTAMP to BOOL.

Examples

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

See also: Datetime functions

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 TIMESTAMP, 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 TIMESTAMP));
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.