Constant Values

SQL Constants represent a simple value that doesn't change.

Introduction

There are five categories of constants in CockroachDB:

• String literals: these define string values but their actual data type willbe inferred from context, for example, 'hello'.
• Numeric literals: these define numeric values but their actual datatype will be inferred from context, for example, -12.3.
• Bit array literals: these define bit array values with data typeBIT, for example, B'1001001'.
• Byte array literals: these define byte array values with data typeBYTES, for example, b'hello'.
• Interpreted literals: these define arbitrary values with an explicittype, for example, INTERVAL '3 days'.
• Named constants: these have predefined values with a predefinedtype, for example, TRUE or NULL.

String literals

CockroachDB supports two formats for string literals:

• Standard SQL string literals.
• String literals with C escape sequences.
  These format also allow arbitrary Unicode characters encoded as UTF-8.

In any case, the actual data type of a string literal is determinedusing the context where it appears.

For example:

In general, the data type of a string literal is that demanded by thecontext if there is no ambiguity, or STRING otherwise.

Check our blog formore information about the typing of string literals.

Standard SQL string literals

SQL string literals are formed by an arbitrary sequence of charactersenclosed between single quotes ('), for example, 'hello world'.

To include a single quote in the string, use a double single quote.For example:

> SELECT 'hello' as a, 'it''s a beautiful day' as b;

+-------+----------------------+
|   a   |          b           |
+-------+----------------------+
| hello | it's a beautiful day |
+-------+----------------------+

For compatibility with the SQL standard, CockroachDB also recognizesthe following special syntax: two simple string literals separated bya newline character are automatically concatenated together to form asingle constant. For example:

> SELECT 'hello'
' world!' as a;

+--------------+
|      a       |
+--------------+
| hello world! |
+--------------+

This special syntax only works if the two simple literals areseparated by a newline character. For example 'hello' ' world!'doesn't work. This is mandated by the SQL standard.

String literals with character escapes

CockroachDB also supports string literals containing escape sequenceslike in the programming language C. These are constructed by prefixingthe string literal with the letter e, for example,e'hello\nworld!'.

The following escape sequences are supported:

For example, the e'x61\141\u0061' escape string represents thehexadecimal byte, octal byte, and 16-bit hexadecimal Unicode charactervalues equivalent to the 'aaa' string literal.

Numeric literals

Numeric literals can have the following forms:

[+-]9999
[+-]9999.[9999][e[+-]999]
[+-][9999].9999[e[+-]999]
[+-]9999e[+-]999
[+-]0xAAAA

Some examples:

+4269
3.1415
-.001
6.626e-34
50e6
0xcafe111

The actual data type of a numeric constant depends both on the contextwhere it is used, its literal format, and its numeric value.

Of the possible data types, which one is actually used is then furtherrefined depending on context.

Check our blog formore information about the typing of numeric literals.

Bit array literals

Bit array literals consist of the B prefix followed by a string ofbinary digits (bits) enclosed in single quotes.

For example: B'1001010101'

Bit array literals are acceptable both when values of typesBIT or VARBIT (BIT VARYING) areexpected.

The number of bits is arbitrary. An empty bit array is denoted B'';the number of bits need not be a multiple of 8, and bit arrays cancontain more than 64 bits.

Byte array literals

CockroachDB supports two formats for byte array literals:

• Byte array literals with C escape sequences
• Hexadecimal-encoded byte array literals

Byte array literals with character escapes

This uses the same syntax as string literals containing character escapes,using a b prefix instead of e. Any character escapes are interpreted like theywould be for string literals.

For example: b'hello,\x32world'

The two differences between byte array literals and string literalswith character escapes are as follows:

• Byte array literals always have data type BYTES, whereas the datatype of a string literal depends on context.
• Byte array literals may contain invalid UTF-8 byte sequences,whereas string literals must always contain valid UTF-8 sequences.

Hexadecimal-encoded byte array literals

This is a CockroachDB-specific extension to express byte arrayliterals: the delimiter x' followed by an arbitrary sequence ofhexadecimal digits, followed by a closing '.

For example, all the following formats are equivalent to b'cat':

• x'636174'
• X'636174'

Interpreted literals

A constant of any data type can be formed using either of the following formats:

type 'string'
'string':::type

The value of the string part is used as input for the conversion function to thespecified data type, and the result is used as a constant with that data type.

Examples:

DATE '2013-12-23'
BOOL 'FALSE'
'42.69':::INT
'TRUE':::BOOL
'3 days':::INTERVAL

Additionally, for compatibility with PostgreSQL, the notation'string'::type and CAST('string' AS type) is also recognized as aninterpreted literal. These are special cases ofcast expressions.

For more information about the allowable format of interpretedliterals, refer to the "Syntax" section of the respective data types:DATE, INET, INTERVAL, TIME,TIMESTAMP/TIMESTAMPTZ.

Named constants

CockroachDB recognizes the following SQL named constants:

• TRUE and FALSE, the two possible values of data type BOOL.
• NULL, the special SQL symbol that indicates "no value present".
  Note that NULL is a valid constant for any type: its actual datatype during expression evaluation is determined based on context.

See also

• Scalar Expressions
• Data Types

Was this page helpful?
YesNo