SQL
Name Qualification
The fully qualified name of catalog objects contains the *catalog*, the *schema* and the *name* of the object. For example:  
Attach the database `new_db`:  
```sql
ATTACH 'new_db.db';
```  
Create the schema `my_schema` in the database `new_db`:  
```sql
CREATE SCHEMA new_db.my_schema;
```  
Create the table `my_table` in the schema `my_schema`:  
```sql
CREATE TABLE new_db.my_schema.my_table (col INTEGER);
```  
Refer to the column `col` inside the table `my_table`:  
```sql
SELECT new_db.my_schema.my_table.col FROM new_db.my_schema.my_table;
```  
Note that often the fully qualified name is not required. When a name is not fully qualified, the system looks for which entries to reference using the *catalog search path*. The default catalog search path includes the system catalog, the temporary catalog and the initially attached database together with the `main` schema.  
Also note the rules on [identifiers and database names in particular](#docs:sql:dialect:keywords_and_identifiers::database-names).  
#### Default Database and Schema {#docs:sql:statements:attach::default-database-and-schema}  
When a table is created without any qualifications, the table is created in the default schema of the default database. The default database is the database that is launched when the system is created – and the default schema is `main`.  
Create the table `my_table` in the default database:  
```sql
CREATE TABLE my_table (col INTEGER);
```  
#### Changing the Default Database and Schema {#docs:sql:statements:attach::changing-the-default-database-and-schema}  
The default database and schema can be changed using the `USE` command.  
Set the default database schema to `new_db.main`:  
```sql
USE new_db;
```  
Set the default database schema to `new_db.my_schema`:  
```sql
USE new_db.my_schema;
```  
#### Resolving Conflicts {#docs:sql:statements:attach::resolving-conflicts}  
When providing only a single qualification, the system can interpret this as *either* a catalog *or* a schema, as long as there are no conflicts. For example:  
```sql
ATTACH 'new_db.db';
CREATE SCHEMA my_schema;
```  
Creates the table `new_db.main.tbl`:  
```sql
CREATE TABLE new_db.tbl (i INTEGER);
```  
Creates the table `default_db.my_schema.tbl`:  
```sql
CREATE TABLE my_schema.tbl (i INTEGER);
```  
If we create a conflict (i.e., we have both a schema and a catalog with the same name) the system requests that a fully qualified path is used instead:  
```sql
CREATE SCHEMA new_db;
CREATE TABLE new_db.tbl (i INTEGER);
```  
```console
Error: Binder Error: Ambiguous reference to catalog or schema "new_db" -
use a fully qualified path like "memory.new_db"
```  
#### Changing the Catalog Search Path {#docs:sql:statements:attach::changing-the-catalog-search-path}  
The catalog search path can be adjusted by setting the `search_path` configuration option, which uses a comma-separated list of values that will be on the search path. The following example demonstrates searching in two databases:  
```sql
ATTACH ':memory:' AS db1;
ATTACH ':memory:' AS db2;
CREATE table db1.tbl1 (i INTEGER);
CREATE table db2.tbl2 (j INTEGER);
```  
Reference the tables using their fully qualified name:  
```sql
SELECT * FROM db1.tbl1;
SELECT * FROM db2.tbl2;
```  
Or set the search path and reference the tables using their name:  
```sql
SET search_path = 'db1,db2';
SELECT * FROM tbl1;
SELECT * FROM tbl2;
```