How to Create a Database in PostgreSQL: Simple Steps

To create a database in PostgreSQL, use the CREATE DATABASE command followed by the database name. You must have the necessary privileges, and the command is run inside the PostgreSQL command-line tool or a SQL client.

Syntax

The basic syntax to create a database in PostgreSQL is:

• CREATE DATABASE database_name; - creates a new database with the specified name.
• You can optionally specify the owner, encoding, and template.

sql

CREATE DATABASE database_name;

Example

This example creates a database named mydb. Run this command in the PostgreSQL command-line tool (psql) or any SQL client connected to your PostgreSQL server.

sql

CREATE DATABASE mydb;

Output

CREATE DATABASE

Common Pitfalls

Common mistakes when creating a database include:

• Trying to create a database with a name that already exists.
• Not having the required privileges to create a database.
• Running the command outside of a PostgreSQL environment.

Example of a wrong command and the correct way:

sql

-- Wrong: creating a database that already exists
CREATE DATABASE mydb;

-- Right: check if database exists or use a different name
-- PostgreSQL does not support IF NOT EXISTS for CREATE DATABASE
-- So you must check manually or handle errors in your application

Quick Reference

Command	Description
CREATE DATABASE dbname;	Creates a new database named dbname.
DROP DATABASE dbname;	Deletes the database named dbname.
\l	Lists all databases in psql command-line tool.
\c dbname	Connects to the database named dbname.

Key Takeaways

Use the CREATE DATABASE command followed by the database name to create a new database.

You must have the right privileges to create a database in PostgreSQL.

Check if a database already exists before creating to avoid errors.

Run commands inside the PostgreSQL environment like psql or a SQL client.

Use \l to list databases and \c to connect to a database in psql.