Arithmetic Operators and Numeric Converter Functions

Arithmetic addition.

db> 

SELECT 2 + 2;

{4}

Arithmetic subtraction.

db> 

SELECT 3 - 2;

{1}

Arithmetic negation.

db> 

SELECT -5;

{-5}

Arithmetic multiplication.

db> 

SELECT 2 * 10;

{20}

Arithmetic division.

db> 

SELECT 10 / 4;

{2.5}

Division by zero results in an error:

db> 

SELECT 10 / 0;

DivisionByZeroError: division by zero

Floor division.

The result is rounded down to the nearest integer. It is equivalent to using regular division and the applying math::floor() to the result.

db> 

SELECT 10 // 4;

{2}

db> 

SELECT math::floor(10 / 4);

{2}

db> 

SELECT -10 // 4;

{-3}

It also works on float, bigint, and decimal types. The type of the result corresponds to the type of the operands:

db> 

SELECT 3.7 // 1.1;

{3.0}

db> 

SELECT 3.7n // 1.1n;

{3.0n}

db> 

SELECT 37 // 11;

{3}

Regular division, floor division, and % are related in the following way: A // B = (A - (A % B)) / B.

Remainder from division (modulo).

This is the remainder from floor division. Just as is the case with // the result type of the remainder operator corresponds to the operand type:

db> 

SELECT 10 % 4;

{2}

db> 

SELECT 10n % 4;

{2n}

db> 

SELECT -10 % 4;

{2}

db> 
... 
... 

# floating arithmetic is inexact, so
# we get 0.3999999999999999 instead of 0.4
SELECT 3.7 % 1.1;

{0.3999999999999999}

db> 

SELECT 3.7n % 1.1n;

{0.4n}

db> 

SELECT 37 % 11;

{4}

Regular division, // and % are related in the following way: A // B = (A - (A % B)) / B.

Modulo division by zero results in an error:

db> 

SELECT 10 % 0;

DivisionByZeroError: division by zero

Power operation.

db> 

SELECT 2 ^ 4;

{16}

Create a bigint value.

Parse a bigint from the input s and optional format specification fmt.

db> 

SELECT to_bigint('-000,012,345', 'S099,999,999,999');

{-12345n}

db> 

SELECT to_bigint('31st', '999th');

{31n}

For more details on formatting see here.

Create a decimal value.

Parse a decimal from the input s and optional format specification fmt.

db> 

SELECT to_decimal('-000,012,345', 'S099,999,999,999');

{-12345.0n}

db> 

SELECT to_decimal('-012.345');

{-12.345n}

db> 

SELECT to_decimal('31st', '999th');

{31.0n}

For more details on formatting see here.

Create an int16 value.

Parse an int16 from the input s and optional format specification fmt.

For more details on formatting see here.

Create an int32 value.

Parse an int32 from the input s and optional format specification fmt.

For more details on formatting see here.

Create an int64 value.

Parse an int64 from the input s and optional format specification fmt.

For more details on formatting see here.

Create a float32 value.

Parse a float32 from the input s and optional format specification fmt.

For more details on formatting see here.

Create a float64 value.

Parse a float64 from the input s and optional format specification fmt.

For more details on formatting see here.