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

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.

In contrast to other Raijin data types, queries cannot explicitly create fields using the unsigned BIGINT data type. Instead, the database automatically casts large, non-negative integer values to this data type on demand that exceed the upper limit of the default signed BIGINT data type.

Range: 0 .. 18446744073709551615
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"}

FLOAT

A single precision (4-byte) floating-point data type

Range: 1.40129846432481707e-45 .. 3.40282346638528860e+38

-1.40129846432481707e-45 .. -3.40282346638528860e+38

Aliases: FLOAT4
Examples

SELECT CAST(12.3 AS FLOAT);
CREATE TABLE type_test(fval FLOAT);

DOUBLE

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

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

Aliases: FLOAT8
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 though. Using N does not restrict the string size.

Use single-quoted literals as strings. Double quoting is not allowed for strings; instead, it is used to construct delimited identifiers and JSON fields/values in the corresponding INSERT statement version.

The standard way to use the apostrophe(') inside a string literal is to escape it with another apostrophe symbol. 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, which are an extension to the SQL standard. There are two ways to specify the escape string/identifier:

If the BackslashEscaping configuration parameter is turned on it is allowed to use backslash escapes inside regular string/quoted identifiers
Using 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 (\) begins a C-like backslash escape sequence, in which the combination of backslash and following character(s) represent 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 taken literally. Thus, to include a backslash character, write two backslashes (\\). Also, a single quote can be included in an escape string by writing \' in addition to the normal way of '' ; the same is true for double quotes in the delimited identifiers, i.e. \" can be used instead of standard "".

Both the 4-digit and the 8-digit form of Unicode escape syntax can be used to specify UTF-16 surrogate pairs to compose characters with code points larger than U+FFFF, although the availability of the 8-digit form technically makes this unnecessary. When surrogate pairs are used, they are first combined into a single code point that is then encoded in UTF-8.

If the BackslashEscaping configuration parameter is turned off, then the backslash character (\) inside of regular strings/quoted identifiers (those ones without the E-prefix) is treated as a regular symbol, not a beginning of the escape sequence. So, the sequence \' or \" would be treated as the end of a string constant or delimited identifier. To write a quote inside a regular string or identifier duplicate the quote '' (or "" for identifiers).

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

You cannot cast DATETIME to BOOL.

Examples

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

See also: Datetime functions

BINARY

This data type was not implemented yet.

Aliases: VARBINARY

INTERVAL

This data type was not implemented yet.