Skip to main content

Lexical structure

Comments

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 in-sensitive and adhere to the general convention for identifier denotation in programming languages.

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.

Literal Constants

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.

Temporal Constants

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

Special Characters

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.

Operator Precedences

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
- right unary minus
* / % left multiplication, division, modulo
+ - left unary addition, subtraction
IS, IS TRUE, IS FALSE, IS UNKNOWN    
IS NULL, IS NOT NULL   test for (not) null
IN   set membership
BETWEEN   range containment
OVERLAPS   time interval overlap
LIKE, ILIKE   string pattern matching
< >   less than, greater than
= right equality, assignment
NOT right logical negation
AND left logical conjunction
OR left logical disjunction
^ left logical exclusive disjunction

Value Expressions

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.