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();
dbname

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

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

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

-- 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
42

-- 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
43

You can also reference local databases in the same MotherDuck queries. This type of query is known as a hybrid query.

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