Object name resolution

Fully qualfied naming convention

Fully qualified names (FQN) in MotherDuck are of the form <database>.<schema>.<object>. Fully qualified naming convention allows you to query objects in MotherDuck regardless of context. Queryable objects can be tables and views.

For example:

SELECT * FROM mydatabase.myschema.mytable;

Fully qualified naming convention is useful when you want your SQL to execute reliably across multiple interfaces, by various users, or in programmatic scripts.

Relative naming convention

For convenience, MotherDuck enables you to omit database or schema when querying objects.

When database is omitted, MotherDuck will attempt to resolve the query by using the current database:

SELECT * FROM myschema.mytable;

When both database and schema are omitted, MotherDuck will first attempt to find the object in the current schema. Thereafter, it will attempt to find the object in other schemas in the current database. If the object name is ambiguous - for example if multiple tables with the same name exist in the database - MotherDuck will return an error:

SELECT * FROM mytable;

You may also choose to omit just the schema. MotherDuck will first search the current schema, and thereafter will search for the object across all other schemas in the specified database:

SELECT * FROM mydatabase.mytable;