SQL Schemas

A schema is a named group of objects inside a database. Tables, views, sequences, and macros all live in one. Two schemas can each hold a table called orders without conflict, because the full name of an object includes the schema it sits in.

create schema reporting;

create table reporting.category_revenue as
select p.category_id,
       sum(oi.quantity * oi.unit_price) as revenue
from order_items oi
join products p on p.product_id = oi.product_id
group by p.category_id;

select * from reporting.category_revenue
order by revenue desc, category_id;

Objects you create without naming a schema go into main, which is where everything on the rest of this site lives.

Qualified names

A fully qualified name has three parts: database.schema.object. You will mostly write two, and often one.

Query an object in another schema without qualifying it and DuckDB reports Table with name category_revenue does not exist!, usually with a Did you mean "reporting.category_revenue"? hint, which is the fastest way to spot that you forgot the prefix.

Switching schemas

USE changes which schema unqualified names resolve to, so you can stop typing the prefix while working inside one area.

use reporting;

-- no prefix needed now
select * from category_revenue;

-- and back
use memory.main;

Tables in main stay reachable while you are in another schema, so USE adds a place to look rather than cutting you off from the rest of the database. select current_schema() tells you where you are.

What to name them

The convention most teams land on describes how finished the data is, not who owns it:

The value is that the schema name answers "can I trust this table?" before anyone opens it.

Schemas vs databases

A database contains schemas; a schema contains objects. One query can join across schemas in the same database without ceremony. Crossing databases means attaching the other one first, and in DuckDB that is a separate ATTACH step. If you are deciding between the two, schemas are almost always what you want: they are lighter, and joins stay simple.

Listing what exists

select schema_name
from duckdb_schemas()
where not internal
order by schema_name;

select table_schema, table_name
from information_schema.tables
where table_schema not in ('information_schema')
order by table_schema, table_name;

Cleaning up

Dropping a schema that still contains objects fails with a Dependency Error. CASCADE removes the schema and everything inside it, which makes a named schema the tidiest possible sandbox: do the work in it, drop it in one statement, forget nothing.

drop schema sandbox cascade

See DROP statements for what CASCADE does and does not reach. You cannot drop main; DuckDB reports it as an internal system entry.