Switching the current database
Below are examples of how to determine the current/active database and schema and switch between different databases and schemas:
- CLI
-- check your current database
> SELECT current_database();
dbname
-- list all tables in the current database
> SHOW TABLES;
table1
table2
-- list all databases
> SHOW DATABASES;
dbname
dbname2
-- switch to database named 'dbname2'
> USE dbname2;
-- verify that you've successfully switched databases
> SELECT current_database();
dbname2
-- check your current schema
> SELECT current_schema();
main
-- list all schemas across all databases
> SELECT * FROM duckdb_schemas();
oid | database_name | database_oid | schema_name | internal | sql |
---|---|---|---|---|---|
986 | my_db | 989 | information_schema | true | NULL |
974 | my_db | 989 | main | false | NULL |
972 | my_db | 989 | my_schema | false | NULL |
987 | my_db | 989 | pg_catalog | true | NULL |
1508 | system | 0 | information_schema | true | NULL |
0 | system | 0 | main | true | NULL |
1509 | system | 0 | pg_catalog | true | NULL |
1510 | temp | 1453 | information_schema | true | NULL |
1454 | temp | 1453 | main | true | NULL |
1511 | temp | 1453 | pg_catalog | true | NULL |
-- switch to schema my_schema within the same database
> USE my_schema;
-- verify that you've successfully switched schemas
> SELECT current_schema();
my_schema
-- switch to database my_db and schema main
> USE my_db.my_schema
-- verify that both the database and schema have been changed
> SELECT current_database(), current_schema();
current_database() | current_schema() |
---|---|
my_db | main |