CARVIEW |
Select Language
HTTP/2 200
date: Sun, 12 Oct 2025 01:39:34 GMT
content-type: text/html; charset=utf-8
nel: {"report_to":"cf-nel","success_fraction":0.0,"max_age":604800}
server: cloudflare
x-origin-cache: HIT
last-modified: Sat, 11 Oct 2025 06:54:09 GMT
access-control-allow-origin: *
expires: Sun, 12 Oct 2025 01:49:34 GMT
cache-control: max-age=600
report-to: {"group":"cf-nel","max_age":604800,"endpoints":[{"url":"https://a.nel.cloudflare.com/report/v4?s=BlXhw2xhXZAfgLiMKY5vIi5%2FnTKY0751yzwgr5Vp2KWr%2BuOxGzQHgUX9ThKS5F9tpx9aSMGR1%2F%2F%2B8S7etBusE17p%2Fd4prZST4g%3D%3D"}]}
x-proxy-cache: MISS
x-github-request-id: A376:2A5FA0:483BB:5B868:68EB06CE
age: 0
via: 1.1 varnish
x-served-by: cache-bom-vanm7210075-BOM
x-cache: MISS
x-cache-hits: 0
x-timer: S1760233174.249802,VS0,VE289
vary: Accept-Encoding
x-fastly-request-id: e78ca4b49431cbf43f2bbbc7c25f454921c14271
cf-cache-status: DYNAMIC
content-encoding: gzip
cf-ray: 98d2e25adb92c1a6-BLR
alt-svc: h3=":443"; ma=86400
Utility Functions – DuckDB
Search Shortcut cmd + k | ctrl + k
- Installation
- Documentation
- Getting Started
- Connect
- Data Import
- Overview
- Data Sources
- CSV Files
- 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
- Parquet Files
- Partitioning
- Appender
- INSERT Statements
- Client APIs
- Overview
- Tertiary Clients
- ADBC
- 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
- Safe Mode
- Autocomplete
- Syntax Highlighting
- Known Issues
- Dart
- Go
- Java (JDBC)
- Julia
- Node.js (Deprecated)
- Node.js (Neo)
- ODBC
- PHP
- 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
- SQL
- Introduction
- Statements
- Overview
- ANALYZE
- ALTER DATABASE
- 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
- MERGE INTO
- 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 Expression
- Casting
- Collations
- Comparisons
- IN Operator
- Logical Operators
- Star Expression
- Subqueries
- TRY
- 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
- DuckDB's SQL Dialect
- Overview
- Indexing
- Friendly SQL
- Keywords and Identifiers
- Order Preservation
- PostgreSQL Compatibility
- SQL Quirks
- Samples
- Configuration
- Extensions
- Overview
- Installing Extensions
- Advanced Installation Methods
- Distributing Extensions
- Versioning of Extensions
- Troubleshooting of Extensions
- Core Extensions
- Overview
- AutoComplete
- Avro
- AWS
- Azure
- Delta
- DuckLake
- Encodings
- Excel
- Full Text Search
- httpfs (HTTP and S3)
- Iceberg
- Overview
- Iceberg REST Catalogs
- Amazon S3 Tables
- Amazon SageMaker Lakehouse (AWS Glue)
- Troubleshooting
- ICU
- inet
- jemalloc
- MySQL
- PostgreSQL
- Spatial
- SQLite
- TPC-DS
- TPC-H
- UI
- VSS
- Guides
- Overview
- Data Viewers
- Database Integration
- 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
- File Access with the file: Protocol
- 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
- Fastly Object Storage Import
- Meta Queries
- Describe Table
- EXPLAIN: Inspect Query Plans
- EXPLAIN ANALYZE: Profile Queries
- List Tables
- Summarize
- DuckDB Environment
- ODBC
- Performance
- Overview
- Environment
- Import
- Schema
- Indexing
- Join Operations
- File Formats
- How to Tune Workloads
- My Workload Is Slow
- Benchmarks
- Working with Huge Databases
- Python
- Installation
- Executing SQL
- Jupyter Notebooks
- marimo 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
- SQL Features
- Snippets
- Creating Synthetic Data
- Dutch Railway Datasets
- Sharing Macros
- Analyzing a Git Repository
- Importing Duckbox Tables
- Copying an In-Memory Database to a File
- Troubleshooting
- Glossary of Terms
- Browsing Offline
- Operations Manual
- Overview
- DuckDB's Footprint
- Logging
- Securing DuckDB
- Non-Deterministic Behavior
- Limits
- DuckDB Docker Container
- Development
- DuckDB Repositories
- Profiling
- Building DuckDB
- Overview
- Build Configuration
- Building Extensions
- Android
- Linux
- macOS
- Raspberry Pi
- Windows
- Python
- R
- Troubleshooting
- Unofficial and Unsupported Platforms
- Benchmark Suite
- Testing
- Internals
- Sitemap
- Live Demo
Documentation
/ SQL
/ Functions
Utility Functions
Scalar Utility Functions
The functions below are difficult to categorize into specific function types and are broadly useful.
Name | Description |
---|---|
alias(column) |
Return the name of the column. |
can_cast_implicitly(source_value, target_value) |
Whether or not we can implicitly cast from the types of the source value to the target value. |
checkpoint(database) |
Synchronize WAL with file for (optional) database without interrupting transactions. |
coalesce(expr, ...) |
Return the first expression that evaluates to a non-NULL value. Accepts 1 or more parameters. Each expression can be a column, literal value, function result, or many others. |
constant_or_null(arg1, arg2) |
If arg2 is NULL , return NULL . Otherwise, return arg1 . |
count_if(x) |
Aggregate function; rows contribute 1 if x is true or a non-zero number, else 0. |
create_sort_key(parameters...) |
Constructs a binary-comparable sort key based on a set of input parameters and sort qualifiers. |
current_catalog() |
Return the name of the currently active catalog. Default is memory. |
current_database() |
Return the name of the currently active database. |
current_query() |
Return the current query as a string. |
current_schema() |
Return the name of the currently active schema. Default is main. |
current_schemas(boolean) |
Return list of schemas. Pass a parameter of true to include implicit schemas. |
current_setting('setting_name') |
Return the current value of the configuration setting. |
currval('sequence_name') |
Return the current value of the sequence. Note that nextval must be called at least once prior to calling currval . |
error(message) |
Throws the given error message . |
equi_width_bins(min, max, bincount, nice := false) |
Returns the upper boundaries of a partition of the interval [min, max] into bin_count equal-sized subintervals (for use with, e.g., histogram ). If nice = true , then min , max , and bincount may be adjusted to produce more aesthetically pleasing results. |
force_checkpoint(database) |
Synchronize WAL with file for (optional) database interrupting transactions. |
gen_random_uuid() |
Return a random UUID similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687 . |
getenv(var) |
Returns the value of the environment variable var . Only available in the command line client. |
hash(value) |
Returns a UBIGINT with a hash of value . The used hash function may change across DuckDB versions. |
icu_sort_key(string, collator) |
Surrogate sort key used to sort special characters according to the specific locale. Collator parameter is optional. Only available when the ICU extension is installed. |
if(a, b, c) |
Ternary conditional operator. |
ifnull(expr, other) |
A two-argument version of coalesce. |
is_histogram_other_bin(arg) |
Returns true when arg is the "catch-all element" of its datatype for the purpose of the histogram_exact function, which is equal to the "right-most boundary" of its datatype for the purpose of the histogram function. |
md5(string) |
Returns the MD5 hash of the string as a VARCHAR . |
md5_number(string) |
Returns the MD5 hash of the string as a UHUGEINT . |
md5_number_lower(string) |
Returns the lower 64-bit segment of the MD5 hash of the string as a UBIGINT . |
md5_number_upper(string) |
Returns the upper 64-bit segment of the MD5 hash of the string as a UBIGINT . |
nextval('sequence_name') |
Return the following value of the sequence. |
nullif(a, b) |
Return NULL if a = b , else return a . Equivalent to CASE WHEN a = b THEN NULL ELSE a END . |
pg_typeof(expression) |
Returns the lower case name of the data type of the result of the expression. For PostgreSQL compatibility. |
query( query_string ) |
Table function that parses and executes the query defined in query_string . Only constant strings are allowed. Warning: this function allows invoking arbitrary queries, potentially altering the database state. |
query_table( tbl_name ) |
Table function that returns the table given in tbl_name . |
query_table( tbl_names , [ by_name ]) |
Table function that returns the union of tables given in tbl_names . If the optional by_name parameter is set to true , it uses UNION ALL BY NAME semantics. |
read_blob(source) |
Returns the content from source (a filename, a list of filenames, or a glob pattern) as a BLOB . See the read_blob guide for more details. |
read_text(source) |
Returns the content from source (a filename, a list of filenames, or a glob pattern) as a VARCHAR . The file content is first validated to be valid UTF-8. If read_text attempts to read a file with invalid UTF-8 an error is thrown suggesting to use read_blob instead. See the read_text guide for more details. |
sha1(string) |
Returns a VARCHAR with the SHA-1 hash of the string . |
sha256(string) |
Returns a VARCHAR with the SHA-256 hash of the string . |
stats(expression) |
Returns a string with statistics about the expression. Expression can be a column, constant, or SQL expression. |
txid_current() |
Returns the current transaction's identifier, a BIGINT value. It will assign a new one if the current transaction does not have one already. |
typeof(expression) |
Returns the name of the data type of the result of the expression. |
uuid() |
Return a random UUID (UUIDv4) similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687 . |
uuidv4() |
Return a random UUID (UUIDv4) similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687 . |
uuidv7() |
Return a random UUIDv7 similar to this: 81964ebe-00b1-7e1d-b0f9-43c29b6fb8f5 . |
uuid_extract_timestamp(uuidv7) |
Extracts TIMESTAMP WITH TIME ZONE from a UUIDv7 value. |
uuid_extract_version(uuid) |
Extracts UUID version (4 or 7 ). |
version() |
Return the currently active version of DuckDB in this format. |
alias(column)
Description | Return the name of the column. |
Example | alias(column1) |
Result | column1 |
can_cast_implicitly(source_value, target_value)
Description | Whether or not we can implicitly cast from the types of the source value to the target value. |
Example | can_cast_implicitly(1::BIGINT, 1::SMALLINT) |
Result | false |
checkpoint(database)
Description | Synchronize WAL with file for (optional) database without interrupting transactions. |
Example | checkpoint(my_db) |
Result | success Boolean |
coalesce(expr, ...)
Description | Return the first expression that evaluates to a non-NULL value. Accepts 1 or more parameters. Each expression can be a column, literal value, function result, or many others. |
Example | coalesce(NULL, NULL, 'default_string') |
Result | default_string |
constant_or_null(arg1, arg2)
Description | If arg2 is NULL , return NULL . Otherwise, return arg1 . |
Example | constant_or_null(42, NULL) |
Result | NULL |
count_if(x)
Description | Aggregate function; rows contribute 1 if x is true or a non-zero number, else 0. |
Example | count_if(42) |
Result | 1 |
create_sort_key(parameters...)
Description | Constructs a binary-comparable sort key based on a set of input parameters and sort qualifiers. |
Example | create_sort_key('abc', 'ASC NULLS FIRST'); |
Result | \x02bcd\x00 |
current_catalog()
Description | Return the name of the currently active catalog. Default is memory. |
Example | current_catalog() |
Result | memory |
current_database()
Description | Return the name of the currently active database. |
Example | current_database() |
Result | memory |
current_query()
Description | Return the current query as a string. |
Example | current_query() |
Result | SELECT current_query(); |
current_schema()
Description | Return the name of the currently active schema. Default is main. |
Example | current_schema() |
Result | main |
current_schemas(boolean)
Description | Return list of schemas. Pass a parameter of true to include implicit schemas. |
Example | current_schemas(true) |
Result | ['temp', 'main', 'pg_catalog'] |
current_setting('setting_name')
Description | Return the current value of the configuration setting. |
Example | current_setting('access_mode') |
Result | automatic |
currval('sequence_name')
Description | Return the current value of the sequence. Note that nextval must be called at least once prior to calling currval . |
Example | currval('my_sequence_name') |
Result | 1 |
error(message)
Description | Throws the given error message . |
Example | error('access_mode') |
equi_width_bins(min, max, bincount, nice := false)
Description | Returns the upper boundaries of a partition of the interval [min, max] into bin_count equal-sized subintervals (for use with, e.g., histogram ). If nice = true , then min , max , and bincount may be adjusted to produce more aesthetically pleasing results. |
Example | equi_width_bins(0.1, 2.7, 4, true) |
Result | [0.5, 1.0, 1.5, 2.0, 2.5, 3.0] |
force_checkpoint(database)
Description | Synchronize WAL with file for (optional) database interrupting transactions. |
Example | force_checkpoint(my_db) |
Result | success Boolean |
gen_random_uuid()
Description | Return a random UUID (UUIDv4) similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687 . |
Example | gen_random_uuid() |
Result | various |
getenv(var)
Description | Returns the value of the environment variable var . Only available in the command line client. |
Example | getenv('HOME') |
Result | /path/to/user/home |
hash(value)
Description | Returns a UBIGINT with the hash of the value . The used hash function may change across DuckDB versions. |
Example | hash('🦆') |
Result | 2595805878642663834 |
icu_sort_key(string, collator)
Description | Surrogate sort key used to sort special characters according to the specific locale. Collator parameter is optional. Only available when the ICU extension is installed. |
Example | icu_sort_key('ö', 'DE') |
Result | 460145960106 |
if(a, b, c)
Description | Ternary conditional operator; returns b if a, else returns c. Equivalent to CASE WHEN a THEN b ELSE c END . |
Example | if(2 > 1, 3, 4) |
Result | 3 |
ifnull(expr, other)
Description | A two-argument version of coalesce. |
Example | ifnull(NULL, 'default_string') |
Result | default_string |
is_histogram_other_bin(arg)
Description | Returns true when arg is the "catch-all element" of its datatype for the purpose of the histogram_exact function, which is equal to the "right-most boundary" of its datatype for the purpose of the histogram function. |
Example | is_histogram_other_bin('') |
Result | true |
md5(string)
Description | Returns the MD5 hash of the string as a VARCHAR . |
Example | md5('abc') |
Result | 900150983cd24fb0d6963f7d28e17f72 |
md5_number(string)
Description | Returns the MD5 hash of the string as a UHUGEINT . |
Example | md5_number('abc') |
Result | 152195979970564155685860391459828531600 |
md5_number_lower(string)
Description | Returns the lower 8 bytes of the MD5 hash of string as a UBIGINT . |
Example | md5_number_lower('abc') |
Result | 8250560606382298838 |
md5_number_upper(string)
Description | Returns the upper 8 bytes of the MD5 hash of string as a UBIGINT . |
Example | md5_number_upper('abc') |
Result | 12704604231530709392 |
nextval('sequence_name')
Description | Return the following value of the sequence. |
Example | nextval('my_sequence_name') |
Result | 2 |
nullif(a, b)
Description | Return NULL if a = b, else return a. Equivalent to CASE WHEN a = b THEN NULL ELSE a END . |
Example | nullif(1+1, 2) |
Result | NULL |
pg_typeof(expression)
Description | Returns the lower case name of the data type of the result of the expression. For PostgreSQL compatibility. |
Example | pg_typeof('abc') |
Result | varchar |
query(query_string)
Description | Table function that parses and executes the query defined in query_string . Only constant strings are allowed. Warning: this function allows invoking arbitrary queries, potentially altering the database state. |
Example | query('SELECT 42 AS x') |
Result | 42 |
query_table(tbl_name)
Description | Table function that returns the table given in tbl_name . |
Example | query_table('t1') |
Result | (the rows of t1 ) |
query_table(tbl_names, [by_name])
Description | Table function that returns the union of tables given in tbl_names . If the optional by_name parameter is set to true , it uses UNION ALL BY NAME semantics. |
Example | query_table(['t1', 't2']) |
Result | (the union of the two tables) |
read_blob(source)
Description | Returns the content from source (a filename, a list of filenames, or a glob pattern) as a BLOB . See the read_blob guide for more details. |
Example | read_blob('hello.bin') |
Result | hello\x0A |
read_text(source)
Description | Returns the content from source (a filename, a list of filenames, or a glob pattern) as a VARCHAR . The file content is first validated to be valid UTF-8. If read_text attempts to read a file with invalid UTF-8 an error is thrown suggesting to use read_blob instead. See the read_text guide for more details. |
Example | read_text('hello.txt') |
Result | hello\n |
sha1(string)
Description | Returns a VARCHAR with the SHA-1 hash of the string . |
Example | sha1('🦆') |
Result | 949bf843dc338be348fb9525d1eb535d31241d76 |
sha256(string)
Description | Returns a VARCHAR with the SHA-256 hash of the string . |
Example | sha256('🦆') |
Result | d7a5c5e0d1d94c32218539e7e47d4ba9c3c7b77d61332fb60d633dde89e473fb |
stats(expression)
Description | Returns a string with statistics about the expression. Expression can be a column, constant, or SQL expression. |
Example | stats(5) |
Result | '[Min: 5, Max: 5][Has Null: false]' |
txid_current()
Description | Returns the current transaction's identifier, a BIGINT value. It will assign a new one if the current transaction does not have one already. |
Example | txid_current() |
Result | various |
typeof(expression)
Description | Returns the name of the data type of the result of the expression. |
Example | typeof('abc') |
Result | VARCHAR |
uuid()
Description | Return a random UUID (UUIDv4) similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687 . |
Example | uuid() |
Result | various |
uuidv4()
Description | Return a random UUID (UUIDv4) similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687 . |
Example | uuidv4() |
Result | various |
uuidv7()
Description | Return a random UUIDv7 similar to this: 81964ebe-00b1-7e1d-b0f9-43c29b6fb8f5 . |
Example | uuidv7() |
Result | various |
uuid_extract_timestamp(uuidv7)
Description | Extracts TIMESTAMP WITH TIME ZONE from a UUIDv7 value. |
Example | uuid_extract_timestamp(uuidv7()) |
Result | 2025-04-19 15:51:20.07+00 |
uuid_extract_version(uuid)
Description | Extracts UUID version (4 or 7 ). |
Example | uuid_extract_version(uuidv7()) |
Result | 7 |
version()
Description | Return the currently active version of DuckDB in this format. |
Example | version() |
Result | various |
Utility Table Functions
A table function is used in place of a table in a FROM
clause.
Name | Description |
---|---|
glob(search_path) |
Return filenames found at the location indicated by the search_path in a single column named file . The search_path may contain glob pattern matching syntax. |
repeat_row(varargs, num_rows) |
Returns a table with num_rows rows, each containing the fields defined in varargs . |
glob(search_path)
Description | Return filenames found at the location indicated by the search_path in a single column named file . The search_path may contain glob pattern matching syntax. |
Example | glob('*') |
Result | (table of filenames) |
repeat_row(varargs, num_rows)
Description | Returns a table with num_rows rows, each containing the fields defined in varargs . |
Example | repeat_row(1, 2, 'foo', num_rows = 3) |
Result | 3 rows of 1, 2, 'foo' |
About this page
© 2025 DuckDB Foundation, Amsterdam NL