SQL CREATE DATABASE

What does the SQL CREATE DATABASE statement do?

Creates a new, named database inside the current server instance.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CREATE DATABASE: Supported: PostgreSQL, MySQL, MariaDB, SQL Server, IBM Db2. Partially supported: Oracle (syntax differs). Not applicable: SQLite (uses ATTACH).

SQL CREATE DATABASE Full Explanation

CREATE DATABASE is a data definition language (DDL) statement that initializes a completely separate logical database, including its system catalog, default encoding, locale, and optional tablespace. Once executed, the server allocates the physical files or directories required to store the new database. Only superusers or roles with CREATEDB privileges can run it. The command does not switch the current session into the new database; clients must issue a separate CONNECT or \c command. Some options are dialect-specific, so verify support before use.

SQL CREATE DATABASE Syntax

CREATE DATABASE database_name
    [WITH]
    [OWNER = user_name]
    [TEMPLATE = template_db]
    [ENCODING = 'encoding']
    [LC_COLLATE = 'locale']
    [LC_CTYPE   = 'locale']
    [TABLESPACE = tablespace_name]
    [CONNECTION LIMIT = max_connections];

SQL CREATE DATABASE Parameters

database_name (identifier) - Name of the database to create; must be unique within the cluster.
OWNER (role name) - Role that will own the database; defaults to the issuing user.
TEMPLATE (identifier) - Existing database to clone. Use template0 for a pristine catalog.
ENCODING (string) - Character set such as 'UTF8', 'LATIN1'.
LC_COLLATE (string) - Locale rules for string comparison.
LC_CTYPE (string) - Locale rules for character classification.
TABLESPACE (identifier) - Where to store the database files.
CONNECTION LIMIT (integer) - Maximum concurrent connections; -1 means unlimited.

Example Queries Using SQL CREATE DATABASE

-- Basic example
CREATE DATABASE sales;

-- Create with specific owner and UTF8 encoding
CREATE DATABASE reporting
    WITH OWNER = analyst_team
         ENCODING = 'UTF8';

-- Clone an existing database
CREATE DATABASE staging FROM TEMPLATE production_backup;

-- Limit connections to 20
CREATE DATABASE sandbox
    WITH CONNECTION LIMIT = 20;

Expected Output Using SQL CREATE DATABASE

The server writes new physical files, registers the database in pg_database (or equivalent system catalog), and returns CREATE DATABASE
No result set is produced

Use Cases with SQL CREATE DATABASE

Provision a fresh database for a new microservice.
Spin up isolated staging or test environments by cloning production.
Separate multi-tenant customer data into dedicated databases.
Manage data retention by archiving old data into a new read-only database.

Common Mistakes with SQL CREATE DATABASE

Forgetting to CONNECT to the new database after creation.
Trying to create a database while inside a transaction block (disallowed in PostgreSQL).
Omitting quotes around string parameters like ENCODING, causing syntax errors.
Creating a database with the same name as an existing one.
Assuming CREATE DATABASE is part of the SQL standard—support and options vary by vendor.

First Introduced In

PostgreSQL 6.0 (1997)

Frequently Asked Questions

Who is allowed to execute CREATE DATABASE?

Only superusers or roles granted the CREATEDB privilege can create new databases. Regular users receive an error unless privileges are elevated.

Does CREATE DATABASE automatically connect me to the new database?

No. The command finishes in the current database. Issue CONNECT database_name or \c database_name to start using the new database.

Can I drop a template database used in CREATE DATABASE?

Yes, but only after no active sessions reference it. Dropping a template removes its ability to serve as the source for new databases.

How do I change a database owner after creation?

Use ALTER DATABASE database_name OWNER TO new_owner; You must be a superuser or the current owner to run this command.