- 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
- C
- Overview
- Startup
- Configuration
- Query
- Data Chunks
- Vectors
- Values
- Types
- Prepared Statements
- Appender
- Table Functions
- Replacement Scans
- API Reference
- C++
- CLI
- Go
- Java
- Julia
- Node.js
- 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
- ADBC
- ODBC
- SQL
- Introduction
- Statements
- Overview
- ANALYZE
- ALTER TABLE
- ALTER VIEW
- ATTACH / 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 / IMPORT DATABASE
- INSERT
- PIVOT
- Profiling
- SELECT
- SET / RESET
- SET VARIABLE
- SUMMARIZE
- Transaction Management
- UNPIVOT
- UPDATE
- USE
- VACUUM
- LOAD / INSTALL
- Query Syntax
- SELECT
- FROM & 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
- DuckDB's SQL Dialect
- Samples
- Configuration
- 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)
- Iceberg
- ICU
- inet
- jemalloc
- MySQL
- PostgreSQL
- Spatial
- SQLite
- Substrait
- TPC-DS
- TPC-H
- 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
- Network & 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
- 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
- SQL Features
- Snippets
- Glossary of Terms
- Browse Offline
- Operations Manual
- Overview
- Limits
- Non-Deterministic Behavior
- Embedding DuckDB
- DuckDB's Footprint
- Securing DuckDB
- Development
- DuckDB Repositories
- Testing
- Overview
- sqllogictest Introduction
- Writing Tests
- Debugging
- Result Verification
- Persistent Testing
- Loops
- Multiple Connections
- Catch
- Profiling
- Release Calendar
- Building
- Benchmark Suite
- Internals
- Sitemap
- Why DuckDB
- Media
- FAQ
- Code of Conduct
- Live Demo
DuckDB offers a collection of table functions that provide metadata about the current database. These functions reside in the main
schema and their names are prefixed with duckdb_
.
The resultset returned by a duckdb_
table function may be used just like an ordinary table or view. For example, you can use a duckdb_
function call in the FROM
clause of a SELECT
statement, and you may refer to the columns of its returned resultset elsewhere in the statement, for example in the WHERE
clause.
Table functions are still functions, and you should write parenthesis after the function name to call it to obtain its returned resultset:
SELECT * FROM duckdb_settings();
Alternatively, you may execute table functions also using the CALL
-syntax:
CALL duckdb_settings();
In this case too, the parentheses are mandatory.
For some of the
duckdb_%
functions, there is also an identically named view available, which also resides in themain
schema. Typically, these views do aSELECT
on theduckdb_
table function with the same name, while filtering out those objects that are marked as internal. We mention it here, because if you accidentally omit the parentheses in yourduckdb_
table function call, you might still get a result, but from the identically named view.
Example:
The duckdb_views()
table function returns all views, including those marked internal:
SELECT * FROM duckdb_views();
The duckdb_views
view returns views that are not marked as internal:
SELECT * FROM duckdb_views;
duckdb_columns
The duckdb_columns()
function provides metadata about the columns available in the DuckDB instance.
Column | Description | Type |
---|---|---|
database_name |
The name of the database that contains the column object. | VARCHAR |
database_oid |
Internal identifier of the database that contains the column object. | BIGINT |
schema_name |
The SQL name of the schema that contains the table object that defines this column. | VARCHAR |
schema_oid |
Internal identifier of the schema object that contains the table of the column. | BIGINT |
table_name |
The SQL name of the table that defines the column. | VARCHAR |
table_oid |
Internal identifier (name) of the table object that defines the column. | BIGINT |
column_name |
The SQL name of the column. | VARCHAR |
column_index |
The unique position of the column within its table. | INTEGER |
internal |
true if this column built-in, false if it is user-defined. |
BOOLEAN |
column_default |
The default value of the column (expressed in SQL) | VARCHAR |
is_nullable |
true if the column can hold NULL values; false if the column cannot hold NULL -values. |
BOOLEAN |
data_type |
The name of the column datatype. | VARCHAR |
data_type_id |
The internal identifier of the column data type. | BIGINT |
character_maximum_length |
Always NULL . DuckDB text types do not enforce a value length restriction based on a length type parameter. |
INTEGER |
numeric_precision |
The number of units (in the base indicated by numeric_precision_radix ) used for storing column values. For integral and approximate numeric types, this is the number of bits. For decimal types, this is the number of digits positions. |
INTEGER |
numeric_precision_radix |
The number-base of the units in the numeric_precision column. For integral and approximate numeric types, this is 2 , indicating the precision is expressed as a number of bits. For the decimal type this is 10 , indicating the precision is expressed as a number of decimal positions. |
INTEGER |
numeric_scale |
Applicable to decimal type. Indicates the maximum number of fractional digits (i.e., the number of digits that may appear after the decimal separator). |
INTEGER |
The information_schema.columns
system view provides a more standardized way to obtain metadata about database columns, but the duckdb_columns
function also returns metadata about DuckDB internal objects. (In fact, information_schema.columns
is implemented as a query on top of duckdb_columns()
)
duckdb_constraints
The duckdb_constraints()
function provides metadata about the constraints available in the DuckDB instance.
Column | Description | Type |
---|---|---|
database_name |
The name of the database that contains the constraint. | VARCHAR |
database_oid |
Internal identifier of the database that contains the constraint. | BIGINT |
schema_name |
The SQL name of the schema that contains the table on which the constraint is defined. | VARCHAR |
schema_oid |
Internal identifier of the schema object that contains the table on which the constraint is defined. | BIGINT |
table_name |
The SQL name of the table on which the constraint is defined. | VARCHAR |
table_oid |
Internal identifier (name) of the table object on which the constraint is defined. | BIGINT |
constraint_index |
Indicates the position of the constraint as it appears in its table definition. | BIGINT |
constraint_type |
Indicates the type of constraint. Applicable values are CHECK , FOREIGN KEY , PRIMARY KEY , NOT NULL , UNIQUE . |
VARCHAR |
constraint_text |
The definition of the constraint expressed as a SQL-phrase. (Not necessarily a complete or syntactically valid DDL-statement.) | VARCHAR |
expression |
If constraint is a check constraint, the definition of the condition being checked, otherwise NULL . |
VARCHAR |
constraint_column_indexes |
An array of table column indexes referring to the columns that appear in the constraint definition. | BIGINT[] |
constraint_column_names |
An array of table column names appearing in the constraint definition. | VARCHAR[] |
The information_schema.referential_constraints
and information_schema.table_constraints
system views provide a more standardized way to obtain metadata about constraints, but the duckdb_constraints
function also returns metadata about DuckDB internal objects. (In fact, information_schema.referential_constraints
and information_schema.table_constraints
are implemented as a query on top of duckdb_constraints()
)
duckdb_databases
The duckdb_databases()
function lists the databases that are accessible from within the current DuckDB process.
Apart from the database associated at startup, the list also includes databases that were attached later on to the DuckDB process
Column | Description | Type |
---|---|---|
database_name |
The name of the database, or the alias if the database was attached using an ALIAS-clause. | VARCHAR |
database_oid |
The internal identifier of the database. | VARCHAR |
path |
The file path associated with the database. | VARCHAR |
internal |
true indicates a system or built-in database. False indicates a user-defined database. |
BOOLEAN |
type |
The type indicates the type of RDBMS implemented by the attached database. For DuckDB databases, that value is duckdb . |
VARCHAR |
duckdb_dependencies
The duckdb_dependencies()
function provides metadata about the dependencies available in the DuckDB instance.
Column | Description | Type |
---|---|---|
classid |
Always 0 | BIGINT |
objid |
The internal id of the object. | BIGINT |
objsubid |
Always 0 | INTEGER |
refclassid |
Always 0 | BIGINT |
refobjid |
The internal id of the dependent object. | BIGINT |
refobjsubid |
Always 0 | INTEGER |
deptype |
The type of dependency. Either regular (n) or automatic (a). | VARCHAR |
duckdb_extensions
The duckdb_extensions()
function provides metadata about the extensions available in the DuckDB instance.
Column | Description | Type |
---|---|---|
extension_name |
The name of the extension. | VARCHAR |
loaded |
true if the extension is loaded, false if it's not loaded. |
BOOLEAN |
installed |
true if the extension is installed, false if it's not installed. |
BOOLEAN |
install_path |
(BUILT-IN) if the extension is built-in, otherwise, the filesystem path where binary that implements the extension resides. |
VARCHAR |
description |
Human readable text that describes the extension's functionality. | VARCHAR |
aliases |
List of alternative names for this extension. | VARCHAR[] |
duckdb_functions
The duckdb_functions()
function provides metadata about the functions (including macros) available in the DuckDB instance.
Column | Description | Type |
---|---|---|
database_name |
The name of the database that contains this function. | VARCHAR |
schema_name |
The SQL name of the schema where the function resides. | VARCHAR |
function_name |
The SQL name of the function. | VARCHAR |
function_type |
The function kind. Value is one of: table ,scalar ,aggregate ,pragma ,macro |
VARCHAR |
description |
Description of this function (always NULL ) |
VARCHAR |
return_type |
The logical data type name of the returned value. Applicable for scalar and aggregate functions. | VARCHAR |
parameters |
If the function has parameters, the list of parameter names. | VARCHAR[] |
parameter_types |
If the function has parameters, a list of logical data type names corresponding to the parameter list. | VARCHAR[] |
varargs |
The name of the data type in case the function has a variable number of arguments, or NULL if the function does not have a variable number of arguments. |
VARCHAR |
macro_definition |
If this is a macro, the SQL expression that defines it. | VARCHAR |
has_side_effects |
false if this is a pure function. true if this function changes the database state (like sequence functions nextval() and curval() ). |
BOOLEAN |
function_oid |
The internal identifier for this function | BIGINT |
duckdb_indexes
The duckdb_indexes()
function provides metadata about secondary indexes available in the DuckDB instance.
Column | Description | Type |
---|---|---|
database_name |
The name of the database that contains this index. | VARCHAR |
database_oid |
Internal identifier of the database containing the index. | BIGINT |
schema_name |
The SQL name of the schema that contains the table with the secondary index. | VARCHAR |
schema_oid |
Internal identifier of the schema object. | BIGINT |
index_name |
The SQL name of this secondary index. | VARCHAR |
index_oid |
The object identifier of this index. | BIGINT |
table_name |
The name of the table with the index. | VARCHAR |
table_oid |
Internal identifier (name) of the table object. | BIGINT |
is_unique |
true if the index was created with the UNIQUE modifier, false if it was not. |
BOOLEAN |
is_primary |
Always false |
BOOLEAN |
expressions |
Always NULL |
VARCHAR |
sql |
The definition of the index, expressed as a CREATE INDEX SQL statement. |
VARCHAR |
Note that duckdb_indexes
only provides metadata about secondary indexes – i.e., those indexes created by explicit CREATE INDEX
statements. Primary keys, foreign keys, and UNIQUE
constraints are maintained using indexes, but their details are included in the duckdb_constraints()
function.
duckdb_keywords
The duckdb_keywords()
function provides metadata about DuckDB's keywords and reserved words.
Column | Description | Type |
---|---|---|
keyword_name |
The keyword. | VARCHAR |
keyword_category |
Indicates the category of the keyword. Values are column_name , reserved , type_function and unreserved . |
VARCHAR |
duckdb_memory
The duckdb_memory()
function provides metadata about DuckDB's buffer manager.
Column | Description | Type |
---|---|---|
tag |
The memory tag. It has one of the following values: BASE_TABLE , HASH_TABLE , PARQUET_READER , CSV_READER , ORDER_BY , ART_INDEX , COLUMN_DATA , METADATA , OVERFLOW_STRINGS , IN_MEMORY_TABLE , ALLOCATOR , EXTENSION . |
VARCHAR |
memory_usage_bytes |
The memory used (in bytes). | BIGINT |
temporary_storage_bytes |
The disk storage used (in bytes). | BIGINT |
duckdb_optimizers
The duckdb_optimizers()
function provides metadata about the optimization rules (e.g., expression_rewriter
, filter_pushdown
) available in the DuckDB instance.
These can be selectively turned off using PRAGMA disabled_optimizers
.
Column | Description | Type |
---|---|---|
name |
The name of the optimization rule. | VARCHAR |
duckdb_schemas
The duckdb_schemas()
function provides metadata about the schemas available in the DuckDB instance.
Column | Description | Type |
---|---|---|
oid |
Internal identifier of the schema object. | BIGINT |
database_name |
The name of the database that contains this schema. | VARCHAR |
database_oid |
Internal identifier of the database containing the schema. | BIGINT |
schema_name |
The SQL name of the schema. | VARCHAR |
internal |
true if this is an internal (built-in) schema, false if this is a user-defined schema. |
BOOLEAN |
sql |
Always NULL |
VARCHAR |
The information_schema.schemata
system view provides a more standardized way to obtain metadata about database schemas.
duckdb_secrets
The duckdb_secrets()
function provides metadata about the secrets available in the DuckDB instance.
Column | Description | Type |
---|---|---|
name |
The name of the secret. | VARCHAR |
type |
The type of the secret, e.g., S3 , GCS , R2 , AZURE . |
VARCHAR |
provider |
The provider of the secret. | VARCHAR |
persistent |
Denotes whether the secret is persisent. | BOOLEAN |
storage |
The backend for storing the secret. | VARCHAR |
scope |
The scope of the secret. | VARCHAR[] |
secret_string |
Returns the content of the secret as a string. Sensitive pieces of information, e.g., they access key, are redacted. | VARCHAR |
duckdb_sequences
The duckdb_sequences()
function provides metadata about the sequences available in the DuckDB instance.
Column | Description | Type |
---|---|---|
database_name |
The name of the database that contains this sequence | VARCHAR |
database_oid |
Internal identifier of the database containing the sequence. | BIGINT |
schema_name |
The SQL name of the schema that contains the sequence object. | VARCHAR |
schema_oid |
Internal identifier of the schema object that contains the sequence object. | BIGINT |
sequence_name |
The SQL name that identifies the sequence within the schema. | VARCHAR |
sequence_oid |
The internal identifier of this sequence object. | BIGINT |
temporary |
Whether this sequence is temporary. Temporary sequences are transient and only visible within the current connection. | BOOLEAN |
start_value |
The initial value of the sequence. This value will be returned when nextval() is called for the very first time on this sequence. |
BIGINT |
min_value |
The minimum value of the sequence. | BIGINT |
max_value |
The maximum value of the sequence. | BIGINT |
increment_by |
The value that is added to the current value of the sequence to draw the next value from the sequence. | BIGINT |
cycle |
Whether the sequence should start over when drawing the next value would result in a value outside the range. | BOOLEAN |
last_value |
null if no value was ever drawn from the sequence using nextval(...) . 1 if a value was drawn. |
BIGINT |
sql |
The definition of this object, expressed as SQL DDL-statement. | VARCHAR |
Attributes like temporary
, start_value
etc. correspond to the various options available in the CREATE SEQUENCE
statement and are documented there in full. Note that the attributes will always be filled out in the duckdb_sequences
resultset, even if they were not explicitly specified in the CREATE SEQUENCE
statement.
The column name
last_value
suggests that it contains the last value that was drawn from the sequence, but that is not the case. It's eithernull
if a value was never drawn from the sequence, or1
(when there was a value drawn, ever, from the sequence).If the sequence cycles, then the sequence will start over from the boundary of its range, not necessarily from the value specified as start value.
duckdb_settings
The duckdb_settings()
function provides metadata about the settings available in the DuckDB instance.
Column | Description | Type |
---|---|---|
name |
Name of the setting. | VARCHAR |
value |
Current value of the setting. | VARCHAR |
description |
A description of the setting. | VARCHAR |
input_type |
The logical datatype of the setting's value. | VARCHAR |
The various settings are described in the configuration page.
duckdb_tables
The duckdb_tables()
function provides metadata about the base tables available in the DuckDB instance.
Column | Description | Type |
---|---|---|
database_name |
The name of the database that contains this table | VARCHAR |
database_oid |
Internal identifier of the database containing the table. | BIGINT |
schema_name |
The SQL name of the schema that contains the base table. | VARCHAR |
schema_oid |
Internal identifier of the schema object that contains the base table. | BIGINT |
table_name |
The SQL name of the base table. | VARCHAR |
table_oid |
Internal identifier of the base table object. | BIGINT |
internal |
false if this is a user-defined table. |
BOOLEAN |
temporary |
Whether this is a temporary table. Temporary tables are not persisted and only visible within the current connection. | BOOLEAN |
has_primary_key |
true if this table object defines a PRIMARY KEY . |
BOOLEAN |
estimated_size |
The estimated number of rows in the table. | BIGINT |
column_count |
The number of columns defined by this object. | BIGINT |
index_count |
The number of indexes associated with this table. This number includes all secondary indexes, as well as internal indexes generated to maintain PRIMARY KEY and/or UNIQUE constraints. |
BIGINT |
check_constraint_count |
The number of check constraints active on columns within the table. | BIGINT |
sql |
The definition of this object, expressed as SQL CREATE TABLE -statement. |
VARCHAR |
The information_schema.tables
system view provides a more standardized way to obtain metadata about database tables that also includes views. But the resultset returned by duckdb_tables
contains a few columns that are not included in information_schema.tables
.
duckdb_temporary_files
The duckdb_temporary_files()
function provides metadata about the temporary files DuckDB has written to disk, to offload data from memory. This function mostly exists for debugging and testing purposes.
Column | Description | Type |
---|---|---|
path |
The name of the temporary file. | VARCHAR |
size |
The size in bytes of the temporary file. | BIGINT |
duckdb_types
The duckdb_types()
function provides metadata about the data types available in the DuckDB instance.
Column | Description | Type |
---|---|---|
database_name |
The name of the database that contains this schema. | VARCHAR |
database_oid |
Internal identifier of the database that contains the data type. | BIGINT |
schema_name |
The SQL name of the schema containing the type definition. Always main . |
VARCHAR |
schema_oid |
Internal identifier of the schema object. | BIGINT |
type_name |
The name or alias of this data type. | VARCHAR |
type_oid |
The internal identifier of the data type object. If NULL , then this is an alias of the type (as identified by the value in the logical_type column). |
BIGINT |
type_size |
The number of bytes required to represent a value of this type in memory. | BIGINT |
logical_type |
The 'canonical' name of this data type. The same logical_type may be referenced by several types having different type_name s. |
VARCHAR |
type_category |
The category to which this type belongs. Data types within the same category generally expose similar behavior when values of this type are used in expression. For example, the NUMERIC type_category includes integers, decimals, and floating point numbers. |
VARCHAR |
internal |
Whether this is an internal (built-in) or a user object. | BOOLEAN |
duckdb_variables
The duckdb_variables()
function provides metadata about the variables available in the DuckDB instance.
Column | Description | Type |
---|---|---|
name |
The name of the variable, e.g., x . |
VARCHAR |
value |
The value of the variable, e.g. 12 . |
VARCHAR |
type |
The type of the variable, e.g., INTEGER . |
VARCHAR |
duckdb_views
The duckdb_views()
function provides metadata about the views available in the DuckDB instance.
Column | Description | Type |
---|---|---|
database_name |
The name of the database that contains this view. | VARCHAR |
database_oid |
Internal identifier of the database that contains this view. | BIGINT |
schema_name |
The SQL name of the schema where the view resides. | VARCHAR |
schema_oid |
Internal identifier of the schema object that contains the view. | BIGINT |
view_name |
The SQL name of the view object. | VARCHAR |
view_oid |
The internal identifier of this view object. | BIGINT |
internal |
true if this is an internal (built-in) view, false if this is a user-defined view. |
BOOLEAN |
temporary |
true if this is a temporary view. Temporary views are not persistent and are only visible within the current connection. |
BOOLEAN |
column_count |
The number of columns defined by this view object. | BIGINT |
sql |
The definition of this object, expressed as SQL DDL-statement. | VARCHAR |
The information_schema.tables
system view provides a more standardized way to obtain metadata about database views that also includes base tables. But the resultset returned by duckdb_views
contains also definitions of internal view objects as well as a few columns that are not included in information_schema.tables
.
About this page
In this article
- duckdb_columns
- duckdb_constraints
- duckdb_databases
- duckdb_dependencies
- duckdb_extensions
- duckdb_functions
- duckdb_indexes
- duckdb_keywords
- duckdb_memory
- duckdb_optimizers
- duckdb_schemas
- duckdb_secrets
- duckdb_sequences
- duckdb_settings
- duckdb_tables
- duckdb_temporary_files
- duckdb_types
- duckdb_variables
- duckdb_views