Literal Types

SereneDB has special literal types for representing NULL, integer and string literals in queries. These have their own binding and conversion rules.

Null Literals

The NULL literal is denoted with the keyword NULL. The NULL literal can be implicitly converted to any other type.

Integer Literals

Integer literals are denoted as a sequence of one or more decimal digits. At runtime, these result in values of the INTEGER_LITERAL type. INTEGER_LITERAL types can be implicitly converted to any integer type in which the value fits. For example, the integer literal 42 can be implicitly converted to a TINYINT, but the integer literal 1000 cannot be.

Tip

SereneDB does not support hexadecimal or binary literals directly. However, strings or string literals in hexadecimal or binary notation with 0x or 0b prefixes respectively, can be cast to integer types, e.g., '0xFF'::INT = 255 or 0b101::INT = 5.

Other Numeric Literals

Non-integer numeric literals can be denoted with decimal notation, using the period character (.) to separate the integer part and the decimal part of the number. The integer part may be omitted (for example, .50), but the decimal part may not: a trailing-dot literal such as 2. is rejected with a syntax error.

Query

SELECT 1.5;
SELECT .50;
SELECT 2.;

Result

 ?column?----------      1.5
 ?column?----------     0.50
db error: ERROR: syntax error at or near (varies)

Non-integer numeric literals can also be denoted using E notation. In E notation, an integer or decimal literal is followed by an exponential part, which is denoted by e or E, followed by a literal integer indicating the exponent. The exponential part indicates that the preceding value should be multiplied by 10 raised to the power of the exponent:

Query

SELECT 1e2;
SELECT 6.02214e23;
SELECT 1e-10;

Result

 ?column?----------      100
 ?column?---------- (varies)
 ?column?---------- (varies)

Underscores in Numeric Literals

SereneDB's SQL dialect allows using the underscore character _ in numeric literals as an optional separator. The rules for using underscores are as follows:

• Underscores are allowed in integer, decimal, hexadecimal and binary notation.
• Underscores cannot be the first or last character in a literal.
• Underscores have to have an integer/numeric part on either side of them, i.e., there cannot be multiple underscores in a row and underscores cannot appear immediately before or after a decimal or exponent.

Examples:

Query

SELECT 100_000_000;
SELECT '0xFF_FF'::INTEGER;
SELECT 1_2.1_2E0_1;
SELECT '0b0_1_0_1'::INTEGER;

Result

 ?column?----------- 100000000
 int4------- 65535
 ?column?----------    121.2
 int4------    5

String Literals

String literals are delimited using single quotes (', apostrophe) and result in STRING_LITERAL values. Note that double quotes (") cannot be used as string delimiter character: instead, double quotes are used to delimit quoted identifiers.

String Literal Concatenation

SereneDB does not support implicit concatenation of adjacent string literals. Placing two single-quoted literals next to each other results in a syntax error, whether or not a newline separates them:

Query

SELECT 'Hello'    ' '    'World' AS greeting;

Result

db error: ERROR: syntax error at or near (varies)

Query

SELECT 'Hello' ' ' 'World' AS greeting;

Result

db error: ERROR: syntax error at or near "' '"

To concatenate strings, use the || operator explicitly:

Query

SELECT 'Hello'    || ' '    || 'World' AS greeting;

Result

 greeting------------- Hello World

Implicit String Conversion

STRING_LITERAL instances can be implicitly converted to any other type.

For example, we can compare string literals with dates:

Query

SELECT d > '1992-01-01' AS resultFROM (VALUES (DATE '1992-01-01')) t(d);

Result

 result-------- f

However, we cannot compare VARCHAR values with dates.

Query

SELECT d > '1992-01-01'::VARCHARFROM (VALUES (DATE '1992-01-01')) t(d);

Result

db error: ERROR: Cannot compare values of type DATE and type VARCHAR - an explicit cast is required

Escape String Literals

To escape a single quote (apostrophe) character in a string literal, use ''. For example, SELECT '''' AS s returns '.

To enable some common escape sequences, such as \n for the newline character, prefix a string literal with e (or E).

Query

SELECT e'Hello\nworld' AS msg;

Result

 msg-------------- Hello\nworld

The following backslash escape sequences are supported:

Escape sequence	Name	ASCII code
\b	backspace	8
\f	form feed	12
\n	newline	10
\r	carriage return	13
\t	tab	9

Dollar-Quoted String Literals

SereneDB supports dollar-quoted string literals, which are surrounded by double-dollar symbols ($$):

Query

SELECT $$Helloworld$$ AS msg;

Result

 msg-------------- Hello\nworld

Query

SELECT $$The price is $9.95$$ AS msg;

Result

 msg-------------------- The price is $9.95

Even more, you can insert alphanumeric tags in the double-dollar symbols to allow for the use of regular double-dollar symbols within the string literal:

Query

SELECT $tag$ this string can contain newlines,'single quotes',"double quotes",and $$dollar quotes$$ $tag$ AS msg;

Result

 msg-----------------------------------------------------------------------------------------------  this string can contain newlines,\n'single quotes',\n"double quotes",\nand $$dollar quotes$$

Dollar-quoted string literals cannot be concatenated implicitly; use the || operator to join them.