PRAGMA
PRAGMA is a SQL command for reading or setting database engine configuration and inspecting internal metadata, using syntax outside standard SQL DML/DDL.
Overview
PRAGMA statements are a SQLite-originated convention, also adopted by DuckDB, for engine-level configuration and introspection that doesn't fit neatly into standard SELECT/SET syntax. They can report metadata (like a table's schema) or change session settings.
Copy code
PRAGMA table_info('orders'); -- column names, types, nullability
PRAGMA database_list; -- list attached databases
PRAGMA version; -- engine version info
Many PRAGMA statements have an equivalent SELECT * FROM pragma_function_name(...) form, since under the hood they're often implemented as table-producing functions.
DuckDB specifics
DuckDB supports a substantial set of PRAGMA commands for introspection and tuning, including:
Copy code
PRAGMA database_list; -- attached databases and their paths
PRAGMA show_tables; -- tables in the current database
PRAGMA table_info('orders'); -- column-level schema info
PRAGMA version; -- DuckDB version and build hash
PRAGMA memory_limit='4GB'; -- cap memory usage
PRAGMA threads=4; -- cap parallelism
Most DuckDB configuration PRAGMAs have an equivalent SET statement (SET memory_limit = '4GB' is the same as PRAGMA memory_limit='4GB'), and many introspection PRAGMAs are equally usable as table functions, e.g. SELECT * FROM pragma_table_info('orders'), which is more composable since it can be filtered, joined, or aggregated like any other query result.
Related terms
The SELECT statement is the workhorse of SQL, used to retrieve and transform data from tables, views, or other data sources.
metadata →Metadata is information that describes other data.
LIMIT →The LIMIT clause restricts a query's result set to a maximum number of rows, often used with ORDER BY to fetch a top-N subset.
SQL →SQL (Structured Query Language) is the standard language for working with relational databases.
CREATE TABLE statement →The CREATE TABLE statement is a fundamental SQL command that defines a new table in a database, specifying its structure including column names, data types,…
FAQS
For configuration options, PRAGMA option=value and SET option = value are typically interchangeable — PRAGMA is DuckDB's SQLite-inherited syntax, while SET mirrors standard SQL. Introspection PRAGMAs like PRAGMA table_info don't have a SET equivalent since they report information rather than configure behavior.
Run PRAGMA table_info('table_name'), which returns each column's name, type, nullability, default value, and primary key status. The equivalent table-function form is SELECT * FROM pragma_table_info('table_name').
