Comments can be added to query scripts for documentation purposes. MonetDB/SQL supports two forms of comments. Any text beginning with '- -' and up to the end of line is ignored. Furthermore, C-style comments ( /* this is ignored */ ) can be injected in a SQL query where a language token is expected. A language token can be a keyword, identifier, a literal constant or a special character. Tokens should be separated by 'white space' (tab, space, newlines) unless there juxtaposition does not create an ambiguous interpretation. Comments are considered equivalent to white spaces.
Identifiers and Keywords
SQL comes with a large collection of keywords, i.e. names reserved by the committee to designate language constructs. Keywords in MonetDB/SQL are case sensitive only when quoted. Otherwise, the normal behavior of MonetDB is to map unquoted names to lower case. E.g.
select TEST from table will be transformed to
select test from table. If you want to refer to TEST (uppercase) you should quote it
select "TEST" from table.
Users can overrule the interpretation of an identifier as a keyword by encapsulation with double quotes, e.g. select denotes a keyword, while "select" denotes a user defined name. This scheme also permits inclusion of case sensitive names and names with special characters or white space in the names. This may be useful for reporting purposes where you want user friendly column header information. However, it is general advisable to limit the number of the escaped keywords.
Names are used to designate database objects. In that role, they are by default case in-sensitive unless encapsulated by double quotes. The terminal identifier classes distinguished are denoted with the suffix _name.
Numeric constants follow the convention of most programming languages. A numeric constant that contains neither a decimal point or exponential is considered of type integer. The underlying machine determines whether it will be interpreted as a 32- or 64-bit value, although most likely it will be that latter case these days.
String constants in SQL are embraced with single quotes like: 'string data'. If your data contains single quote's, each single quote need to be doubled, so 'a single '' quote in a string' represents string: a single ' quote in a string.
We support Unicode character string literals (from Apr2019 release 11.33.3) using: U&'...' including UESCAPE. For the string literals, you can have U&'...' '...' '...' UESCAPE '...' where the escape must be as single character and the other '...' strings are also Unicode character string literals. For now, these latter strings also undergo C-style backslash interpretation.
We support PostgreSQL-like E'...' strings (from Apr2019 release 11.33.3). The strings can contain C-style backslash escapes.
We support raw strings (from Jun2020 release 11.37.7) by using R'...' or r'...'. This means that C-style backslash escapes will remain uninterpreted within those strings. For instance
SELECT r'\t'; returns a char string of length 2 where
SELECT e'\t'; returns a char string of length 1.
Binary data can be represented in hexadecimal string notation using 2 hexadecimal characters per byte, for example x'10FF' to represent 2 byte values: 16 and 255. This notation can be used with the blob (binary large object) data type, see also BinaryStringFunctions
Often strings can be cast to other types using the CAST( expr AS type ) expression, provided the typed coercion routine is available. For example:
SELECT CAST( '129' AS integer );
SELECT CAST( '129.42' AS decimal );
SELECT CAST( '0.3' AS double );
illustrates conversion of a digits string value into an integer, a decimal and a double precision floating point number.
The DATE, TIME and TIMESTAMP data types come with a straightforward string literal conversion structure, e.g.:
SELECT DATE '2014-02-03', TIME '15:45:56', TIMESTAMP '2014-02-03 15:45:56';
produces a single properly typed row result. It is equivalent to:
SELECT CAST('2014-02-03' AS DATE), CAST ('15:45:56' AS TIME), CAST ('2014-02-03 15:45:56' AS TIMESTAMP);
String literals (currently both the '...' and E'...' styles) may contain the C-style escape characters: '\n' for new lines, '\t' for tab, '\r' for return, and '\\' for backslash. The conventions '\ddd` where d a digit stands for a number denoted in octal.
Most operators in SQL have the same precedence and are left-associative. Parenthesis can be used to disambiguate the precedence order. The operator definitions can not be overloaded, nor can you define new operators.
|.||left||table/column name separator|
|* / %||left||multiplication, division, modulo|
|+ -||left||unary addition, subtraction|
|IS, IS TRUE, IS FALSE, IS UNKNOWN|
|IS NULL, IS NOT NULL||test for (not) null|
|OVERLAPS||time interval overlap|
|LIKE, ILIKE||string pattern matching|
|< >||less than, greater than|
|^||left||logical exclusive disjunction|
The language is built around value- and table- expressions. Value expressions encompass denotation of literal constants, type casts, column references, operator and function invocation, and scalar returning subqueries.
The column references take the form [correlation_name '.'] column_name. The correlation_name is either a table name or an alias introduced in a from clause. The correlation name may be omitted if the column name uniquely identifies a column within the scope of current query.
The table references take the form [schema_name '.'] table_or_view_name [AS alias_name]. Table expressions produce a relational table. It is internally referenced through a correlation name, which supports attribute selection using the '.' denotation.