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;
{"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
orfalse
You do not need to quote these literals. It is possible to use uppercase, lowercase or mixed case literals.
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'
ore"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.
Backslash escape sequence | Interpretation |
---|---|
|
Backspace |
|
Form feed |
|
Newline |
|
Carriage return |
|
Tab |
|
Octal byte value |
|
Hexadecimal byte value |
|
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