Literals

Literals represent constant values.

SQream DB contains the following types of literals:

Numeric Literals

Numeric literals can be expressed as follows:

number_literal ::=
   [+-] digits
   | digits . [ digits ] [ e [+-] digits ]
   | [ digits ] . digits [ e [+-] digits ]
   | digits e[+-]digits

Examples

1234

1234.56

12.

.34

123.56e-45

0.23

3.141

42

Note

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 BIGINT.

Note

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 FLOAT.

String Literals

String literals are string (text) values, encoded either in ASCII or UTF-8.

String literals are surrounded by single quotes (') or dollars ($$)

Tip

To use a single quote in a string, use a repeated single quote.

Examples

'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 VARCHAR.

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 (''):

'Dianne''s horse'.

Note that two adjacent single quotes is not the same as a double-quote character (").

Examples

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.

Examples

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 E.

E'abc'

The character sequence inside the single quotes can contain escaped characters in addition to regular characters, shown below:

Sequence

Interpretation

\b

Inserts a backspace.

\f

Inserts a form feed.

\n

Inserts a newline.

\r

Inserts a carriage return.

\t

Inserts a tab.

\o, \oo, `\ooo` (o = 0 - 7)

Inserts an octal byte value. This sequence is currently not supported.

\xh, \xhh (h = 0 - 9, A - F)

Inserts a hexadecimal byte value. This sequence is currently not supported.

\uxxxx, \Uxxxxxxxx

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

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 cast for more information about supported casts.

Syntax Reference

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 )

Examples

'1955-11-05' :: date

'TRUE' :: BOOL

CAST('2300' as BIGINT)

CAST(42 :: FLOAT)

Boolean Literals

Boolean literals include the keywords true or false.

Example

INSERT INTO animals VALUES ('fox',true), ('cat',true), ('kiwi',false);

Other Constants

The following other constants can be used:

  • TRUE and FALSE - interpreted as values of type BOOL.

  • NULL - which has no type of its own. The type is inferred from context during query compilation.