我的书签
添加书签
移除书签Lexical structure
An SQL input consists of a sequence of commands each of which is a sequence of tokens, terminated by a semicolon (;).
The syntax of a command defines its set of valid tokens. A token can be a key word, an identifier, a quoted identifier, a literal (or constant), or a special character symbol.
String literal
String literals are defined as an arbitrary sequence of characters that are delimited with single quotes ' as defined in ANSI SQL, for example 'This is a string'.
Escape strings
The escape character in CrateDB is the single-quote '. A character gets escaped when adding a single-quote before it. For example a single quote character within a string literal can be included by writing two adjacent single quotes, e.g., 'Jack''s car'.
Note
Two adjacent single quotes are not equivalent to the double-quote character ".
String literals with C-Style escapes
In addition to the escaped character ', CrateDB supports C-Style escaped string sequences. Such a sequence is constructed by prefixing the string literal with the letter E or e, for example, e'hello\nWorld'. The following escaped sequences are supported:
| 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,a-f]) |
hexadecimal byte value |
\uxxxx, \Uxxxxxxxx (x = [0-9,A-F,a-f]) |
16 or 32-bit hexadecimal Unicode character value |
For instance, the escape string literal e'\u0061\x61\141' is equivalent to the 'aaa' string literal.
1. cr> select e'\u0061\x61\141' as col1;
2. +------+
3. | col1 |
4. +------+
5. | aaa |
6. +------+
7. SELECT 1 row in set (... sec)
Any other character following a backslash is taken literally. Thus, to include a backslash character \, two adjacent backslashes need to be used (i.e. \\).
1. cr> select e'aa\\nbb' as col1;
2. +--------+
3. | col1 |
4. +--------+
5. | aa\nbb |
6. +--------+
7. SELECT 1 row in set (... sec)
Finally, a single quote can be included in an escape string literal by also using the escape backslash character: \', in addition to the single-quote described in the string literals section.
1. cr> select e'aa\'bb' as col1;
2. +-------+
3. | col1 |
4. +-------+
5. | aa'bb |
6. +-------+
7. SELECT 1 row in set (... sec)
Key words and identifiers
The table below lists all reserved key words in CrateDB. These need to be quoted if used as identifiers:
1. cr> SELECT word FROM pg_catalog.pg_get_keywords() WHERE catcode = 'R' ORDER BY 1;
2. +-------------------+
3. | word |
4. +-------------------+
5. | add |
6. | all |
7. | alter |
8. | and |
9. | any |
10. | array |
11. | as |
12. | asc |
13. | between |
14. | by |
15. | called |
16. | case |
17. | cast |
18. | column |
19. | constraint |
20. | create |
21. | cross |
22. | current_date |
23. | current_schema |
24. | current_time |
25. | current_timestamp |
26. | current_user |
27. | default |
28. | delete |
29. | deny |
30. | desc |
31. | describe |
32. | directory |
33. | distinct |
34. | drop |
35. | else |
36. | end |
37. | escape |
38. | except |
39. | exists |
40. | extract |
41. | false |
42. | first |
43. | for |
44. | from |
45. | full |
46. | function |
47. | grant |
48. | group |
49. | having |
50. | if |
51. | in |
52. | index |
53. | inner |
54. | input |
55. | insert |
56. | intersect |
57. | into |
58. | is |
59. | join |
60. | last |
61. | left |
62. | like |
63. | limit |
64. | match |
65. | natural |
66. | not |
67. | null |
68. | nulls |
69. | object |
70. | offset |
71. | on |
72. | or |
73. | order |
74. | outer |
75. | persistent |
76. | recursive |
77. | reset |
78. | returns |
79. | revoke |
80. | right |
81. | select |
82. | session_user |
83. | set |
84. | some |
85. | stratify |
86. | substring |
87. | table |
88. | then |
89. | transient |
90. | true |
91. | try_cast |
92. | unbounded |
93. | union |
94. | update |
95. | user |
96. | using |
97. | when |
98. | where |
99. | with |
100. +-------------------+
101. SELECT 95 rows in set (... sec)
Tokens such as my_table, id, name, or data in the example below are identifiers, which identify names of tables, columns, and other database objects.
Example:
1. CREATE TABLE my_table (
2. id INTEGER,
3. name STRING,
4. data OBJECT
5. ) WITH (number_of_replicas = 0);
Note
Key words and unquoted identifiers are case insensitive.
This means that:
1. select foo from t;
is equivalent to:
1. select Foo from t;
or:
1. select FOO from t;
A widely used convention is to write key words in uppercase and identifiers in lowercase, such as
1. ALTER TABLE foo ADD COLUMN new_column INTEGER;
1. INSERT INTO foo (id, name) VALUES (1, 'bar');
Quoted identifiers can contain an arbitrary sequence of characters enclosed by double quotes ("). Quoted identifiers are never keywords, so you can use "update" as a table or column name.
Special characters
Some non-alphanumeric characters do have a special meaning. For their usage please refer to the sections where the respective syntax elements are described.
Semicolon
The semicolon (;) terminates an SQL statement. It cannot appear anywhere else within the command, except within a string or quoted identifier.
Comma
The comma (,) is used in various syntactical elements to separate elements of a list.
Brackets
Square brackets ([]) are used to select elements of arrays and objects, e.g. arr[1] or obj['key'].
Asterisk
The asterisk (*) is used in some contexts to denote all columns of a table. As an argument in global aggregate functions it has the meaning of any field, e.g. COUNT(*).
Period
The period (.) is used for numeric values and to separate schema and table names, e.g. blob.my_blob_table.
Comments
An SQL statement can contain comments. Single line comments start with a double dash (--) and end at the end of that line. Multi line comments start with /* and end with */.
Example:
1. /*
2. * Retrieve information about all tables in the 'doc' schema.
3. */
4. SELECT *
5. FROM information_schema.tables
6. WHERE table_schema = 'doc'; -- query information schema for doc tables
