Skip to main content

Specifying different databases

MotherDuck enables you to specify an active/current database and an active/current schema within that database. Queryable objects (e.g. tables) that belong to the current database are resolved with just <object_name>. MotherDuck will automatically search all schemas within the current database. If there are overlapping names within different schemas, objects can be qualified with <schema_name>.<object_name>.

Queryable objects in your account outside of the active/current database are resolved with <database_name>.<object_name>. However, if a schema in the current database shares the same name as another database, the fully qualified name must be used: <database_name>.<schema_name>.<object_name> (an error will be thrown to indicate the ambiguity). This applies to databases that both live in MotherDuck and in your local DuckDB environment.

For example:

-- check your current database
> SELECT current_database();

-- check your current schema
> SELECT current_schema();

-- query a table mytable that exists in the current database dbname
> SELECT count(*) FROM mytable;

-- query a table mytable2 that exists in the database dbname2
SELECT count(*) FROM dbname2.mytable2;

-- query a table mytable3 that exists in schema2
-- note that the syntax is identical to the database name syntax above and
-- MotherDuck will detect whether a database or schema is involved
SELECT count(*) FROM schema2.mytable3

-- query a table in another database when a schema exists with the same name in the current database
-- (overlappingname is both a database name and a schema name)
SELECT count(*) FROM overlappingname.myschemaname.mytable4

To change the active database, schema, or database/schema combination, execute a USE command. See the documentation on switching the current database for details.