Literals represent constant values.
SQream DB contains the following types of literals:
- Numeric literals - define numbers such as
- String literals - define text values like
'Foxes are cool',
- Typed literals - define values with explicit types like
(3.0 :: float)
- Boolean literals - define values that include
- Other constants - predefined values like
Numeric literals can be expressed as follows:
number_literal ::= [+-] digits | digits . [ digits ] [ e [+-] digits ] | [ digits ] . digits [ e [+-] digits ] | digits e[+-]digits
1234 1234.56 12. .34 123.56e-45 0.23 3.141 42
The actual data type of the value changes based on context, the format used, and the value itself.
For example, any number containing the decimal point will be considered
FLOAT by default.
Any whole number will considered
INT, unless the value is larger than the maximum value, in which case the type will become a
A numeric literal that contains neither a decimal point nor an exponent is considered
INT by default if its value fits in type
INT (32 bits). If not, it is considered
BIGINT by default if its value fits in type
BIGINT (64 bits). If neither are true, it is considered
FLOAT. Literals that contain decimal points and/or exponents are always considered
String literals are string (text) values, encoded either in ASCII or UTF-8.
String literals are surrounded by single quotes (
') or dollars (
To use a single quote in a string, use a repeated single quote.
'This is an example of a string' 'Hello? Is it me you''re looking for?' -- Repeated single quotes are treated as a single quote $$That is my brother's company's CEO's son's dog's toy$$ -- Dollar-quoted '1997-01-01' -- This is a string
The actual data type of the value changes based on context, the format used, and the value itself. In the example below, the first value is interpreted as a
DATE, while the second is interpreted as a
INSERT INTO cool_dates(date_col, reason) VALUES ('1955-11-05', 'Doc Brown discovers flux capacitor');
This section describes the following types of literals:
Regular String Literals¶
In SQL, a regular string literal is a sequence of zero or more characters bound by single quotes (
'This is a string'.
You can include a single-quote character in a string literal with two consecutive single quotes (
Note that two adjacent single quotes is not the same as a double-quote character (
The following are some examples of regular string literals:
'123' 'אבג' 'a''b' ''
Dollar-Quoted String Literals¶
Dollar-quoted string literals consist of a dollar sign (
$), an optional “tag” of zero or more characters, another dollar sign, an arbitrary sequence of characters that make up the string content, a dollar sign, the same tag at the beginning of the dollar quote, and another dollar sign.
For example, below are two different ways to specify the string
Dianne's horse using dollar-quoted string literals:
$$Dianne's horse$$ $<tag>$Dianne's horse$<tag>$
Note that you can use single quotes inside the dollar-quoted string without an escape. Because the string is always written literally, you do not need to escape any characters inside a dollar-quoted string. Backslashes and dollar signs indicate no specific functions unless they are part of a sequence matching the opening tag.
Any used tags in a dollar-quoted string follow the same rules as for unquoted identifiers, except that they cannot contain a dollar sign.
In addition, because tags are case sensitive,
$<tag>$String content$<tag>$ is correct, but
$<TAG>$String content$<tag>$ is incorrect.
A dollar-quoted string that follows a keyword or identifier must be separated from it by whitespace (such as spaces, tabs, or newlines). If you do not separate them with whitespace, the dollar-quoting delimiter is taken as part of the preceding identifier.
Escaped String Literals¶
Because regular string literals do not support inserting special characters (such as new lines), the escaped string literals syntax was added to support inserting special characters with an escaping syntax.
In addition to being enclosed by single quotes (e.g. ‘abc’), escaped string literals are preceded by a capital
The character sequence inside the single quotes can contain escaped characters in addition to regular characters, shown below:
||Inserts a backspace.|
||Inserts a form feed.|
||Inserts a newline.|
||Inserts a carriage return.|
||Inserts a tab.|
||Inserts an octal byte value. This sequence is currently not supported.|
||Inserts a hexadecimal byte value. This sequence is currently not supported.|
||Inserts a 16 or 32-bit hexadecimal unicode character value (x = 0 - 9, A - F).|
Excluding the characters in the table above, escaped string literals take all other characters following a backslash literally. To include a backslash character, use two consecutive backslashes (
\\). You can use a single quote in an escape string by writing
\', in addition to the normal method (
Typed literals allow you to create any data type using either of the following syntaxes:
CAST(literal AS type_name)
literal :: type_name
See also Converting and Casting Types for more information about supported casts.
The following is a syntax reference for typed literals:
typed_literal ::= cast(literal AS type_name) | literal :: type_name literal ::= string_literal | number_literal | NULL | TRUE | FALSE type_name ::= BOOL | TINYINT | SMALLINT | INT | BIGINT | FLOAT | REAL | DATE | DATETIME | VARCHAR ( digits ) | TEXT ( digits )
'1955-11-05' :: date 'TRUE' :: BOOL CAST('2300' as BIGINT) CAST(42 :: FLOAT)
Boolean literals include the keywords
INSERT INTO animals VALUES ('fox',true), ('cat',true), ('kiwi',false);
The following other constants can be used:
FALSE- interpreted as values of type
NULL- which has no type of its own. The type is inferred from context during query compilation.