Search Shortcut cmd + k | ctrl + k

- Installation
- Documentation

- Getting Started
- Connect

- Overview
- Concurrency

- Data Import

- Overview
- Data Sources
- CSV Files

- Overview
- Auto Detection
- Reading Faulty CSV Files
- Tips

- JSON Files

- Overview
- Creating JSON
- Loading JSON
- Writing JSON
- JSON Type
- JSON Functions
- Format Settings
- Installing and Loading
- SQL to / from JSON
- Caveats

- Multiple Files

- Overview
- Combining Schemas

- Parquet Files

- Overview
- Metadata
- Encryption
- Tips

- Partitioning

- Hive Partitioning
- Partitioned Writes

- Appender
- INSERT Statements

- Client APIs

- Overview
- C

- Overview
- Startup
- Configuration
- Query
- Data Chunks
- Vectors
- Values
- Types
- Prepared Statements
- Appender
- Table Functions
- Replacement Scans
- API Reference

- C++
- CLI

- Overview
- Arguments
- Dot Commands
- Output Formats
- Editing
- Autocomplete
- Syntax Highlighting

- Dart
- Go
- Java
- Julia
- Node.js (Neo)

- Overview

- Node.js

- Overview
- API Reference

- Python

- Overview
- Data Ingestion
- Conversion between DuckDB and Python
- DB API
- Relational API
- Function API
- Types API
- Expression API
- Spark API
- API Reference
- Known Python Issues

- R
- Rust
- Swift
- Wasm

- Overview
- Instantiation
- Data Ingestion
- Query
- Extensions

- ADBC
- ODBC

- Overview
- Linux Setup
- Windows Setup
- macOS Setup
- Configuration

- SQL

- Introduction
- Statements

- Overview
- ANALYZE
- ALTER TABLE
- ALTER VIEW
- ATTACH and DETACH
- CALL
- CHECKPOINT
- COMMENT ON
- COPY
- CREATE INDEX
- CREATE MACRO
- CREATE SCHEMA
- CREATE SECRET
- CREATE SEQUENCE
- CREATE TABLE
- CREATE VIEW
- CREATE TYPE
- DELETE
- DESCRIBE
- DROP
- EXPORT and IMPORT DATABASE
- INSERT
- LOAD / INSTALL
- PIVOT
- Profiling
- SELECT
- SET / RESET
- SET VARIABLE
- SUMMARIZE
- Transaction Management
- UNPIVOT
- UPDATE
- USE
- VACUUM

- Query Syntax

- SELECT
- FROM and JOIN
- WHERE
- GROUP BY
- GROUPING SETS
- HAVING
- ORDER BY
- LIMIT and OFFSET
- SAMPLE
- Unnesting
- WITH
- WINDOW
- QUALIFY
- VALUES
- FILTER
- Set Operations
- Prepared Statements

- Data Types

- Overview
- Array
- Bitstring
- Blob
- Boolean
- Date
- Enum
- Interval
- List
- Literal Types
- Map
- NULL Values
- Numeric
- Struct
- Text
- Time
- Timestamp
- Time Zones
- Union
- Typecasting

- Expressions

- Overview
- CASE Statement
- Casting
- Collations
- Comparisons
- IN Operator
- Logical Operators
- Star Expression
- Subqueries

- Functions

- Overview
- Aggregate Functions
- Array Functions
- Bitstring Functions
- Blob Functions
- Date Format Functions
- Date Functions
- Date Part Functions
- Enum Functions
- Interval Functions
- Lambda Functions
- List Functions
- Map Functions
- Nested Functions
- Numeric Functions
- Pattern Matching
- Regular Expressions
- Struct Functions
- Text Functions
- Time Functions
- Timestamp Functions
- Timestamp with Time Zone Functions
- Union Functions
- Utility Functions
- Window Functions

- Constraints
- Indexes
- Meta Queries

- Information Schema
- Metadata Functions

- DuckDB's SQL Dialect

- Overview
- Indexing
- Friendly SQL
- Keywords and Identifiers
- Order Preservation
- PostgreSQL Compatibility

- Samples

- Configuration

- Overview
- Pragmas
- Secrets Manager

- Extensions

- Overview
- Core Extensions
- Community Extensions
- Working with Extensions
- Versioning of Extensions
- Arrow
- AutoComplete
- AWS
- Azure
- Delta
- Excel
- Full Text Search
- httpfs (HTTP and S3)

- Overview
- HTTP(S) Support
- Hugging Face
- S3 API Support
- Legacy Authentication Scheme for S3 API

- Iceberg
- ICU
- inet
- jemalloc
- MySQL
- PostgreSQL
- Spatial

- Overview
- Function Reference
- R-Tree Indexes
- GDAL Integration

- SQLite
- Substrait
- TPC-DS
- TPC-H
- VSS

- Guides

- Overview
- Data Viewers

- Tableau
- CLI Charting with YouPlot

- Database Integration

- Overview
- MySQL Import
- PostgreSQL Import
- SQLite Import

- File Formats

- Overview
- CSV Import
- CSV Export
- Directly Reading Files
- Excel Import
- Excel Export
- JSON Import
- JSON Export
- Parquet Import
- Parquet Export
- Querying Parquet Files

- Network and Cloud Storage

- Overview
- HTTP Parquet Import
- S3 Parquet Import
- S3 Parquet Export
- S3 Iceberg Import
- S3 Express One
- GCS Import
- Cloudflare R2 Import
- DuckDB over HTTPS / S3

- Meta Queries

- Describe Table
- EXPLAIN: Inspect Query Plans
- EXPLAIN ANALYZE: Profile Queries
- List Tables
- Summarize
- DuckDB Environment

- ODBC

- ODBC Guide

- Performance

- Overview
- Environment
- Import
- Schema
- Indexing
- Join Operations
- File Formats
- How to Tune Workloads
- My Workload Is Slow
- Benchmarks

- Python

- Installation
- Executing SQL
- Jupyter Notebooks
- SQL on Pandas
- Import from Pandas
- Export to Pandas
- Import from Numpy
- Export to Numpy
- SQL on Arrow
- Import from Arrow
- Export to Arrow
- Relational API on Pandas
- Multiple Python Threads
- Integration with Ibis
- Integration with Polars
- Using fsspec Filesystems

- SQL Editors

- DBeaver SQL IDE

- SQL Features

- AsOf Join
- Full-Text Search
- query and query_table Functions

- Snippets

- Create Synthetic Data
- Sharing Macros

- Glossary of Terms
- Browse Offline

- Operations Manual

- Overview
- Limits
- Non-Deterministic Behavior
- Embedding DuckDB
- DuckDB's Footprint

- Files Created by DuckDB
- Gitignore for DuckDB
- Reclaiming Space

- Securing DuckDB

- Overview
- Securing Extensions

- Development

- DuckDB Repositories
- Testing

- Overview
- sqllogictest Introduction
- Writing Tests
- Debugging
- Result Verification
- Persistent Testing
- Loops
- Multiple Connections
- Catch

- Profiling
- Release Calendar
- Building

- Overview
- Build Instructions
- Build Configuration
- Building Extensions
- Platforms
- Troubleshooting

- Benchmark Suite

- Internals

- Overview
- Storage Versions and Format
- Execution Format
- Pivot

- Sitemap
- Why DuckDB
- Media
- FAQ
- Code of Conduct
- Live Demo

Numeric Functions

## Numeric Operators

The table below shows the available mathematical operators for numeric types.

Operator | Description | Example | Result
--- | --- | --- | ---
`+` | addition | `2 + 3` | `5`
`-` | subtraction | `2 - 3` | `-1`
`*` | multiplication | `2 * 3` | `6`
`/` | float division | `5 / 2` | `2.5`
`//` | division | `5 // 2` | `2`
`%` | modulo (remainder) | `5 % 4` | `1`
`**` | exponent | `3 ** 4` | `81`
`^` | exponent (alias for `**`) | `3 ^ 4` | `81`
`&` | bitwise AND | `91 & 15` | `11`
`|` | bitwise OR | `32 | 3` | `35`
`<<` | bitwise shift left | `1 << 4` | `16`
`>>` | bitwise shift right | `8 >> 2` | `2`
`~` | bitwise negation | `~15` | `-16`
`!` | factorial of `x` | `4!` | `24`

### Division and Modulo Operators

There are two division operators: `/` and `//`. They are equivalent when at least one of the operands is a `FLOAT` or a `DOUBLE`. When both operands are integers, `/` performs floating points division (`5 / 2 = 2.5`) while `//` performs integer division (`5 // 2 = 2`).

### Supported Types

The modulo, bitwise, and negation and factorial operators work only on integral data types, whereas the others are available for all numeric data types.

## Numeric Functions

The table below shows the available mathematical functions.

Name | Description
--- | ---
`@(x)` | Absolute value. Parentheses are optional if `x` is a column name.
`abs(x)` | Absolute value.
`acos(x)` | Computes the arccosine of `x`.
`add(x, y)` | Alias for `x + y`.
`asin(x)` | Computes the arcsine of `x`.
`atan(x)` | Computes the arctangent of `x`.
`atan2(y, x)` | Computes the arctangent `(y, x)`.
`bit_count(x)` | Returns the number of bits that are set.
`cbrt(x)` | Returns the cube root of the number.
`ceil(x)` | Rounds the number up.
`ceiling(x)` | Rounds the number up. Alias of `ceil`.
`cos(x)` | Computes the cosine of `x`.
`cot(x)` | Computes the cotangent of `x`.
`degrees(x)` | Converts radians to degrees.
`divide(x, y)` | Alias for `x // y`.
`even(x)` | Round to next even number by rounding away from zero.
`exp(x)` | Computes `e ** x`.
`factorial(x)` | See `!` operator. Computes the product of the current integer and all integers below it.
`fdiv(x, y)` | Performs integer division (`x // y`) but returns a `DOUBLE` value.
`floor(x)` | Rounds the number down.
`fmod(x, y)` | Calculates the modulo value. Always returns a `DOUBLE` value.
`gamma(x)` | Interpolation of the factorial of `x - 1`. Fractional inputs are allowed.
`gcd(x, y)` | Computes the greatest common divisor of `x` and `y`.
`greatest_common_divisor(x, y)` | Computes the greatest common divisor of `x` and `y`.
`greatest(x1, x2, ...)` | Selects the largest value.
`isfinite(x)` | Returns true if the floating point value is finite, false otherwise.
`isinf(x)` | Returns true if the floating point value is infinite, false otherwise.
`isnan(x)` | Returns true if the floating point value is not a number, false otherwise.
`lcm(x, y)` | Computes the least common multiple of `x` and `y`.
`least_common_multiple(x, y)` | Computes the least common multiple of `x` and `y`.
`least(x1, x2, ...)` | Selects the smallest value.
`lgamma(x)` | Computes the log of the `gamma` function.
`ln(x)` | Computes the natural logarithm of `x`.
`log(x)` | Computes the base-10 logarithm of `x`.
`log10(x)` | Alias of `log`. Computes the base-10 logarithm of `x`.
`log2(x)` | Computes the base-2 log of `x`.
`multiply(x, y)` | Alias for `x * y`.
`nextafter(x, y)` | Return the next floating point value after `x` in the direction of `y`.
`pi()` | Returns the value of pi.
`pow(x, y)` | Computes `x` to the power of `y`.
`power(x, y)` | Alias of `pow`. computes `x` to the power of `y`.
`radians(x)` | Converts degrees to radians.
`random()` | Returns a random number `x` in the range `0.0 <= x < 1.0`.
`round_even(v NUMERIC, s INTEGER)` | Alias of `roundbankers(v, s)`. Round to `s` decimal places using the rounding half to even rule. Values `s < 0` are allowed.
`round(v NUMERIC, s INTEGER)` | Round to `s` decimal places. Values `s < 0` are allowed.
`setseed(x)` | Sets the seed to be used for the random function.
`sign(x)` | Returns the sign of `x` as -1, 0 or 1.
`signbit(x)` | Returns whether the signbit is set or not.
`sin(x)` | Computes the sin of `x`.
`sqrt(x)` | Returns the square root of the number.
`subtract(x, y)` | Alias for `x - y`.
`tan(x)` | Computes the tangent of `x`.
`trunc(x)` | Truncates the number.
`xor(x, y)` | Bitwise XOR.

#### `@(x)`

Description | Absolute value. Parentheses are optional if `x` is a column name.
Example | `@(-17.4)`
Result | `17.4`
Alias | `abs`

#### `abs(x)`

Description | Absolute value.
Example | `abs(-17.4)`
Result | `17.4`
Alias | `@`

#### `acos(x)`

Description | Computes the arccosine of `x`.
Example | `acos(0.5)`
Result | `1.0471975511965976`

#### `add(x, y)`

Description | Alias for `x + y`.
Example | `add(2, 3)`
Result | `5`

#### `asin(x)`

Description | Computes the arcsine of `x`.
Example | `asin(0.5)`
Result | `0.5235987755982989`

#### `atan(x)`

Description | Computes the arctangent of `x`.
Example | `atan(0.5)`
Result | `0.4636476090008061`

#### `atan2(y, x)`

Description | Computes the arctangent (y, x).
Example | `atan2(0.5, 0.5)`
Result | `0.7853981633974483`

#### `bit_count(x)`

Description | Returns the number of bits that are set.
Example | `bit_count(31)`
Result | `5`

#### `cbrt(x)`

Description | Returns the cube root of the number.
Example | `cbrt(8)`
Result | `2`

#### `ceil(x)`

Description | Rounds the number up.
Example | `ceil(17.4)`
Result | `18`

#### `ceiling(x)`

Description | Rounds the number up. Alias of `ceil`.
Example | `ceiling(17.4)`
Result | `18`

#### `cos(x)`

Description | Computes the cosine of `x`.
Example | `cos(90)`
Result | `-0.4480736161291701`

#### `cot(x)`

Description | Computes the cotangent of `x`.
Example | `cot(0.5)`
Result | `1.830487721712452`

#### `degrees(x)`

Description | Converts radians to degrees.
Example | `degrees(pi())`
Result | `180`

#### `divide(x, y)`

Description | Alias for `x // y`.
Example | `divide(5, 2)`
Result | `2`

#### `even(x)`

Description | Round to next even number by rounding away from zero.
Example | `even(2.9)`
Result | `4`

#### `exp(x)`

Description | Computes `e ** x`.
Example | `exp(0.693)`
Result | `2`

#### `factorial(x)`

Description | See `!` operator. Computes the product of the current integer and all integers below it.
Example | `factorial(4)`
Result | `24`

#### `fdiv(x, y)`

Description | Performs integer division (`x // y`) but returns a `DOUBLE` value.
Example | `fdiv(5, 2)`
Result | `2.0`

#### `floor(x)`

Description | Rounds the number down.
Example | `floor(17.4)`
Result | `17`

#### `fmod(x, y)`

Description | Calculates the modulo value. Always returns a `DOUBLE` value.
Example | `fmod(5, 2)`
Result | `1.0`

#### `gamma(x)`

Description | Interpolation of the factorial of `x - 1`. Fractional inputs are allowed.
Example | `gamma(5.5)`
Result | `52.34277778455352`

#### `gcd(x, y)`

Description | Computes the greatest common divisor of `x` and `y`.
Example | `gcd(42, 57)`
Result | `3`

#### `greatest_common_divisor(x, y)`

Description | Computes the greatest common divisor of `x` and `y`.
Example | `greatest_common_divisor(42, 57)`
Result | `3`

#### `greatest(x1, x2, ...)`

Description | Selects the largest value.
Example | `greatest(3, 2, 4, 4)`
Result | `4`

#### `isfinite(x)`

Description | Returns true if the floating point value is finite, false otherwise.
Example | `isfinite(5.5)`
Result | `true`

#### `isinf(x)`

Description | Returns true if the floating point value is infinite, false otherwise.
Example | `isinf('Infinity'::float)`
Result | `true`

#### `isnan(x)`

Description | Returns true if the floating point value is not a number, false otherwise.
Example | `isnan('NaN'::float)`
Result | `true`

#### `lcm(x, y)`

Description | Computes the least common multiple of `x` and `y`.
Example | `lcm(42, 57)`
Result | `798`

#### `least_common_multiple(x, y)`

Description | Computes the least common multiple of `x` and `y`.
Example | `least_common_multiple(42, 57)`
Result | `798`

#### `least(x1, x2, ...)`

Description | Selects the smallest value.
Example | `least(3, 2, 4, 4)`
Result | `2`

#### `lgamma(x)`

Description | Computes the log of the `gamma` function.
Example | `lgamma(2)`
Result | `0`

#### `ln(x)`

Description | Computes the natural logarithm of `x`.
Example | `ln(2)`
Result | `0.693`

#### `log(x)`

Description | Computes the base-10 log of `x`.
Example | `log(100)`
Result | `2`

#### `log10(x)`

Description | Alias of `log`. Computes the base-10 log of `x`.
Example | `log10(1000)`
Result | `3`

#### `log2(x)`

Description | Computes the base-2 log of `x`.
Example | `log2(8)`
Result | `3`

#### `multiply(x, y)`

Description | Alias for `x * y`.
Example | `multiply(2, 3)`
Result | `6`

#### `nextafter(x, y)`

Description | Return the next floating point value after `x` in the direction of `y`.
Example | `nextafter(1::float, 2::float)`
Result | `1.0000001`

#### `pi()`

Description | Returns the value of pi.
Example | `pi()`
Result | `3.141592653589793`

#### `pow(x, y)`

Description | Computes `x` to the power of `y`.
Example | `pow(2, 3)`
Result | `8`

#### `power(x, y)`

Description | Alias of `pow`. computes `x` to the power of `y`.
Example | `power(2, 3)`
Result | `8`

#### `radians(x)`

Description | Converts degrees to radians.
Example | `radians(90)`
Result | `1.5707963267948966`

#### `random()`

Description | Returns a random number `x` in the range `0.0 <= x < 1.0`.
Example | `random()`
Result | various

#### `round_even(v NUMERIC, s INTEGER)`

Description | Alias of `roundbankers(v, s)`. Round to `s` decimal places using the rounding half to even rule. Values `s < 0` are allowed.
Example | `round_even(24.5, 0)`
Result | `24.0`

#### `round(v NUMERIC, s INTEGER)`

Description | Round to `s` decimal places. Values `s < 0` are allowed.
Example | `round(42.4332, 2)`
Result | `42.43`

#### `setseed(x)`

Description | Sets the seed to be used for the random function.
Example | `setseed(0.42)`

#### `sign(x)`

Description | Returns the sign of `x` as -1, 0 or 1.
Example | `sign(-349)`
Result | `-1`

#### `signbit(x)`

Description | Returns whether the signbit is set or not.
Example | `signbit(-1.0)`
Result | `true`

#### `sin(x)`

Description | Computes the sin of `x`.
Example | `sin(90)`
Result | `0.8939966636005579`

#### `sqrt(x)`

Description | Returns the square root of the number.
Example | `sqrt(9)`
Result | `3`

#### `subtract(x, y)`

Description | Alias for `x - y`.
Example | `subtract(2, 3)`
Result | `-1`

#### `tan(x)`

Description | Computes the tangent of `x`.
Example | `tan(90)`
Result | `-1.995200412208242`

#### `trunc(x)`

Description | Truncates the number.
Example | `trunc(17.4)`
Result | `17`

#### `xor(x, y)`

Description | Bitwise XOR.
Example | `xor(17, 5)`
Result | `20`

##### About this page

- Report content issue
- Edit this page on GitHub