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

BINARY

This data type was not implemented yet.

Aliases

VARBINARY

INTERVAL

This data type was not implemented yet.