Skip to main content

Basics database operations

While embedded DuckDB uses files on your local filesystem to represent databases, MotherDuck implements SQL syntax for creating, listing and dropping databases.

Create database​

-- [OR REPLACE] and [IF NOT EXISTS] are optional modifiers.
CREATE [OR REPLACE | IF NOT EXISTS] DATABASE <database name>;
USE <database name>;

Creating copies of databases in MotherDuck in this manner is a metadata-only operation that copies no data.

Listing databases​

-- returns all connected local and remote databases 
SHOW DATABASES;
-- returns current database    
SELECT current_database();

Delete database​

USE <database name>;
DROP DATABASE <database name>;

Example usage:

> SHOW DATABASES;
test01

-- Let's put two different t1  tables into into two different databases
> CREATE TABLE dbname.t1 AS (SELECT range AS r FROM range(12));
> SELECT * FROM t1;

-- now for the other database
> CREATE DATABASE test02;
> CREATE TABLE test02.t1 AS (SELECT 'test02' AS dbname)

-- show the databases we've created
> SHOW DATABASES;
test01
test02