Value expressions¶
A value expression is a combination of one or more values, operators, and functions that evaluate to a single value.
Table of contents
Literal value¶
A literal is a notation to represent a value within a statement.
Different types have different notations. The simplest forms are:
boolean literals:
true
orfalse
string literals:
'this is a string literal'
numeric literals:
42
,42.0
or with underscores1_000_000
interval literals:
INTERVAL '1' SECOND
See also
Integer literal value¶
Integer literals are a subcategory of literal values
and can be on of the: numeric literals, null
,
a parameter reference, or a
CAST/TRY CAST expression, for which the resulting
datatype can be converted to an integer, e.g.:
123
null
'10'::int
CAST(? AS long)
TRY_CAST(? AS short)
Column reference¶
A column reference is the name of a column. It’s represented using an identifier. An identifier is an unquoted or double quoted string.
unquoted:
columnname
quoted:
"columnName"
It’s also possible to include the name of a table with or without schema and catalog name, or an alias in order to unambiguously identify a column of a specific relation if a statement contains multiple aliases or table definitions:
crate.myschema.mytable.columnname
myschema.mytable.columname
or:
tab0.columnname
See also
Note
As CrateDB doesn’t support multiple catalogs, only multiple schemas, the
only valid catalog name is crate
.
Parameter reference¶
A parameter reference is a placeholder for a value.
CrateDB clients usually have some kind of API to provide those values.
Parameter references can either be unnumbered or numbered:
Question mark as an unnumbered placeholder:
select * from t where x = ?
$n
as numbered placeholder:select * from t where x = $1 or x = $2
Operator invocation¶
An operator can be invoked as a value expression in one of two ways: binary or unary.
The syntax of a binary operator:
expression operator expression
The syntax of a unary operator:
operator expression
Subscripts¶
A subscript expression is an expression which contains a subscript
operator ([ ]
). It can be used to access a sub
value of a composite type value.
Array subscript¶
The subscript operator can be used on array expressions to retrieve a single element of an array:
array_expression[ array_index ]
array_index
is a 1 based integer specifying the position of the element in
the array which should be retrieved.
See also
Object subscript¶
On object expressions the subscript operator can be used to access an inner element of the object:
obj_expression['key']
The key
must be a string literal which is the name of the element which
should be retrieved.
See also
Record subscript¶
Record subscript retrieves the value of a field within a record or object. This is similar to object subscripts.
Synopsis:
(record_expression).fieldName
Example:
cr> SELECT (information_schema._pg_expandarray(ARRAY['a', 'b'])).n AS n
+---+
| n |
+---+
| 1 |
| 2 |
+---+
SELECT 2 rows in set (... sec)
expression
is an expression of type record
or object
and key
is
an identifier that must refer to a field of the record.
Function call¶
A function can be invoked with a function call (a process better known as calling the function). The corresponding syntax is the function name optionally followed by zero or more arguments (in the form of value expressions) enclosed by parentheses:
function_name[([expression [, expression ... ]])]
Type cast¶
A type cast specifies the conversion from one type to another. The syntax is:
CAST(expression as type)
Another variant to do type casts is try_cast
. Instead of raising an error
this returns null
if a value cannot be converted to the given type:
TRY_CAST(expression as type)
See also
Object constructor¶
A object constructor is an expression which builds an object using its arguments.
It consists of one ore more ident = expression
, separated by commas and
enclosed in curly brackets:
{ elementNameIdent = valueExpression [, elementNameIdent = valueExpression ...] }
See also
Array constructor¶
A array constructor is an expression which builds an array. It consists of one
or more expressions separated by commas, enclosed in square brackets and
optionally prefixed with ARRAY
:
[ ARRAY ] '[' expression [, expression ... ] ']'
See also
Another way to construct an array is by using an ARRAY(subquery)
expression
as part of the SELECT list of a SELECT
statement:
ARRAY '(' subquery ')'
Example:
cr> select array(select height from sys.summits order by height desc limit 5)
... as top5_mountains_array;
+--------------------------------+
| top5_mountains_array |
+--------------------------------+
| [4808, 4634, 4545, 4527, 4506] |
+--------------------------------+
SELECT 1 row in set (... sec)
Note
Array constructor only supports subqueries returning a single column.
Scalar subquery¶
A scalar subquery (also known as a subquery expression) is a subquery that returns a single value (i.e., one row with one column).
If zero rows are returned, it will be treated as null value. In the case that more than one row (or more than one column) is returned, CrateDB will treat it as an error.
Scalar subqueries can access columns of its immediate parent if addressed via a table alias. Such a subquery is known as correlated subquery.
cr> SELECT (SELECT t.mountain) as m FROM sys.summits t ORDER BY 1 ASC LIMIT 2;
+--------------+
| m |
+--------------+
| Acherkogel |
| Ackerlspitze |
+--------------+
SELECT 2 rows in set (... sec)
Note
Scalar subqueries are restricted to SELECT, DELETE and UPDATE statements and cannot be used in other statements.
Note
Correlated subqueries are executed via a “Correlated Join”. A correlated join executes the sub-query for each row in the input relation. If the result set of the outer relation is large this can be slow.
Note
Correlated subqueries are currently limited to the select list and where clause of a query.