Documentation Index
Fetch the complete documentation index at: https://private-7c7dfe99-page-updates.mintlify.app/llms.txt
Use this file to discover all available pages before exploring further.
assumeNotNull
Introduced in: v1.1.0
Returns the corresponding non-Nullable value for a value of type Nullable.
If the original value is NULL, an arbitrary result can be returned.
See also: functions ifNull and coalesce.
Syntax
Arguments
x — The original value of any nullable type. Nullable(T)
Returned value
Returns the non-nullable value, if the original value was not NULL, otherwise an arbitrary value, if the input value is NULL. Any
Examples
Usage example
CREATE TABLE t_null (x Int8, y Nullable(Int8))
ENGINE=MergeTree()
ORDER BY x;
INSERT INTO t_null VALUES (1, NULL), (2, 3);
SELECT assumeNotNull(y) FROM table;
SELECT toTypeName(assumeNotNull(y)) FROM t_null;
┌─assumeNotNull(y)─┐
│ 0 │
│ 3 │
└──────────────────┘
┌─toTypeName(assumeNotNull(y))─┐
│ Int8 │
│ Int8 │
└──────────────────────────────┘
coalesce
Introduced in: v1.1.0
Returns the leftmost non-NULL argument.
Syntax
Arguments
x[, y, ...] — Any number of parameters of non-compound type. All parameters must be of mutually compatible data types. Any
Returned value
Returns the first non-NULL argument, otherwise NULL, if all arguments are NULL. Any or NULL
Examples
Usage example
-- Consider a list of contacts that may specify multiple ways to contact a customer.
CREATE TABLE aBook
(
name String,
mail Nullable(String),
phone Nullable(String),
telegram Nullable(UInt32)
)
ENGINE = MergeTree
ORDER BY tuple();
INSERT INTO aBook VALUES ('client 1', NULL, '123-45-67', 123), ('client 2', NULL, NULL, NULL);
-- The mail and phone fields are of type String, but the telegram field is UInt32 so it needs to be converted to String.
-- Get the first available contact method for the customer from the contact list
SELECT name, coalesce(mail, phone, CAST(telegram,'Nullable(String)')) FROM aBook;
┌─name─────┬─coalesce(mail, phone, CAST(telegram, 'Nullable(String)'))─┐
│ client 1 │ 123-45-67 │
│ client 2 │ ᴺᵁᴸᴸ │
└──────────┴───────────────────────────────────────────────────────────┘
firstNonDefault
Introduced in: v25.9.0
Returns the first non-default value from a set of arguments
Syntax
firstNonDefault(arg1[, arg2[ ...]])
arg1 — The first argument to check - arg2 — The second argument to check - ... — Additional arguments to check
Returned value
Result type is the supertype of all arguments
Examples
integers
SELECT firstNonDefault(0, 1, 2)
SELECT firstNonDefault('', 'hello', 'world')
SELECT firstNonDefault(NULL, 0 :: UInt8, 1 :: UInt8)
SELECT firstNonDefault(NULL, 0 :: Nullable(UInt8), 1 :: Nullable(UInt8))
ifNull
Introduced in: v1.1.0
Returns an alternative value if the first argument is NULL.
Syntax
Arguments
x — The value to check for NULL. Anyalt — The value that the function returns if x is NULL. Any
Returned value
Returns the value of x if it is not NULL, otherwise alt. Any
Examples
Usage example
SELECT ifNull('a', 'b'), ifNull(NULL, 'b');
┌─ifNull('a', 'b')─┬─ifNull(NULL, 'b')─┐
│ a │ b │
└──────────────────┴───────────────────┘
isNotNull
Introduced in: v1.1.0
Checks if the argument is not NULL.
Also see: operator IS NOT NULL.
Syntax
Arguments
x — A value of non-compound data type. Any
Returned value
Returns 1 if x is not NULL, otherwise 0. UInt8
Examples
Usage example
CREATE TABLE t_null
(
x Int32,
y Nullable(Int32)
)
ENGINE = MergeTree
ORDER BY tuple();
INSERT INTO t_null VALUES (1, NULL), (2, 3);
SELECT x FROM t_null WHERE isNotNull(y);
isNull
Introduced in: v1.1.0
Checks if the argument is NULL.
Also see: operator IS NULL.
Syntax
Arguments
x — A value of non-compound data type. Any
Returned value
Returns 1 if x is NULL, otherwise 0. UInt8
Examples
Usage example
CREATE TABLE t_null
(
x Int32,
y Nullable(Int32)
)
ENGINE = MergeTree
ORDER BY tuple();
INSERT INTO t_null VALUES (1, NULL), (2, 3);
SELECT x FROM t_null WHERE isNull(y);
isNullable
Introduced in: v22.7.0
Checks whether the argument’s data type is Nullable (i.e it allows NULL values).
Syntax
Arguments
x — A value of any data type. Any
Returned value
Returns 1 if x is of a Nullable data type, otherwise 0. UInt8
Examples
Usage example
CREATE TABLE tab (
ordinary_col UInt32,
nullable_col Nullable(UInt32)
)
ENGINE = MergeTree
ORDER BY tuple();
INSERT INTO tab (ordinary_col, nullable_col) VALUES (1,1), (2, 2), (3,3);
SELECT isNullable(ordinary_col), isNullable(nullable_col) FROM tab;
┌───isNullable(ordinary_col)──┬───isNullable(nullable_col)──┐
│ 0 │ 1 │
│ 0 │ 1 │
│ 0 │ 1 │
└─────────────────────────────┴─────────────────────────────┘
isZeroOrNull
Introduced in: v20.3.0
Checks if the argument is either zero (0) or NULL.
Syntax
Arguments
x — A numeric value. UInt
Returned value
Returns 1 if x is NULL or equal to zero, otherwise 0. UInt8/16/32/64 or Float32/Float64
Examples
Usage example
CREATE TABLE t_null
(
x Int32,
y Nullable(Int32)
)
ENGINE = MergeTree
ORDER BY tuple();
INSERT INTO t_null VALUES (1, NULL), (2, 0), (3, 3);
SELECT x FROM t_null WHERE isZeroOrNull(y);
nullIf
Introduced in: v1.1.0
Returns NULL if both arguments are equal.
Syntax
Arguments
x — The first value. Anyy — The second value. Any
Returned value
Returns NULL if both arguments are equal, otherwise returns the first argument. NULL or Nullable(x)
Examples
Usage example
SELECT nullIf(1, 1), nullIf(1, 2);
┌─nullIf(1, 1)─┬─nullIf(1, 2)─┐
│ ᴺᵁᴸᴸ │ 1 │
└──────────────┴──────────────┘
toNullable
Introduced in: v1.1.0
Converts the provided argument type to Nullable.
Syntax
Arguments
x — A value of any non-compound type. Any
Returned value
Returns the input value but of Nullable type. Nullable(Any)
Examples
Usage example
SELECT toTypeName(10), toTypeName(toNullable(10));
┌─toTypeName(10)─┬─toTypeName(toNullable(10))─┐
│ UInt8 │ Nullable(UInt8) │
└────────────────┴────────────────────────────┘
