CREATE DATABASE

The CREATE DATABASE statement creates a new, empty database. A single SereneDB server can host many independent databases (the default is postgres); switch between them with USE or by connecting to one directly.

Examples

Create a database named app_production:

SQL

DROP DATABASE IF EXISTS app_production;
CREATE DATABASE app_production;

Use IF NOT EXISTS so the statement succeeds even when the database already exists, instead of raising an error:

SQL

CREATE DATABASE IF NOT EXISTS app_production;

Once created, you can connect to it like any other database — for example with psql:

sh

psql -h localhost -p 7890 -d app_production

As an alternative to reconnecting, switch to the new database within the current session with the USE statement:

SQL

USE app_production;

Storage options

WITH sets how the new database stores its tables:

SQL

DROP DATABASE IF EXISTS app_archive;
CREATE DATABASE app_archive WITH (BLOCK_SIZE = 16384, ROW_GROUP_SIZE = 1048576);
SELECT block_size FROM pragma_database_size() WHERE database_name = 'app_archive';

 block_size
------------
      16384

Option	Description	Default
BLOCK_SIZE	The block size of the database file in bytes: a power of two from 16384 to 262144.	262144
ROW_GROUP_SIZE	The number of rows in a row group: a multiple of 2048.	122880

The options are stored with the database, so the server opens it with them again after a restart. BLOCK_SIZE is fixed once the file exists; ROW_GROUP_SIZE applies to the row groups written from then on. Any other option is refused.

A database created inside a transaction is visible to other sessions only once that transaction commits. Until then, creating a database of the same name from another session fails.

Dropping a database

DROP DATABASE [IF EXISTS] name [CASCADE | RESTRICT] removes a database together with its tables, indexes and files. With IF EXISTS, dropping a missing database is not an error. As in PostgreSQL, it cannot run inside a transaction block and cannot be rolled back:

SQL

BEGIN;
DROP DATABASE app_scratch;
ROLLBACK;
DROP DATABASE app_scratch;
SELECT count(*) AS n FROM pg_database WHERE datname = 'app_scratch';
DROP DATABASE IF EXISTS app_scratch;

error db error: ERROR: DROP DATABASE cannot run inside a transaction block

 n
---
 0

Only the owner of a database, or a member of the owning role, can drop it. A database that other sessions are connected to cannot be dropped: as in PostgreSQL, DROP DATABASE gives them five seconds to disconnect and fails if any is still connected then. Neither can the database the session itself is connected to or has switched to with USE be dropped:

SQL

DROP DATABASE app_production;
# DOCS_TEST_END
USE postgres;
DROP DATABASE app_production;
DROP DATABASE app_archive;

error db error: ERROR: cannot drop the currently open database

DROP DATABASE is rejected while objects of other databases depend on objects of this one, unless CASCADE is given, which drops those dependents too; see Dependencies across Databases. A database created with CREATE DATABASE cannot be removed with DETACH.

A transaction that wrote to a database dropped by another session fails at commit.

A database that has subscriptions cannot be dropped; drop its subscriptions first.

See also

• ATTACH / DETACH — attach an existing database file
• USE — switch the active database

Syntax

Syntax