Create a PostgreSQL Database: Step-by-Step Guide

Updated 6 November 2026 · Standard formula, shown below

This guide walks you through creating a database in PostgreSQL, from connecting to the server to verifying your new database is ready. You’ll learn the essential SQL commands and psql shortcuts, whether you’re on Windows, macOS, or Linux. By the end, you’ll be able to create, list, and connect to a PostgreSQL database with confidence.

What You’ll Need

PostgreSQL installed on your machine (version 10 or later recommended).
Access to a terminal or command prompt.
The psql command-line tool (comes with PostgreSQL).
Superuser or createdb privileges (the default postgres user usually has these).

Step-by-Step

Open a terminal (Command Prompt on Windows, Terminal on macOS/Linux).
Connect to PostgreSQL as the default superuser. Run:
psql -U postgres
You’ll be prompted for the password you set during installation. If you’re on Linux and using peer authentication, you might need to switch to the postgres system user first: sudo -u postgres psql.
Check that you’re in the psql shell. You should see a prompt like postgres=#.
Create a new database with the CREATE DATABASE command. For example, to create a database named mydb:
CREATE DATABASE mydb;
Note the semicolon at the end – it’s required. The database name should be lowercase, without spaces or hyphens (use underscores if needed).
Verify the database was created. List all databases with:
\l
You should see mydb in the list.
Connect to your new database with:
\c mydb
Your prompt should change to mydb=#, indicating you’re now connected.
Exit psql when done:
\q

Common Mistakes

Forgetting the semicolon – SQL commands won’t execute without it. You’ll see a continuation prompt (mydb-#) instead of a result.
Using quotes around the database name – CREATE DATABASE "mydb"; creates a database with mixed-case or spaces, which is harder to work with. Use lowercase unquoted names.
Creating a database that already exists – PostgreSQL will throw an error. You can use CREATE DATABASE mydb IF NOT EXISTS; (though this is not standard SQL, it works in PostgreSQL).
Not having the right privileges – If you get ERROR: permission denied to create database, you need to be a superuser or have the CREATEDB privilege.

Tips

Use a naming convention – e.g., lowercase with underscores (user_accounts) for readability and compatibility.
Set a default encoding – PostgreSQL defaults to UTF-8, which is fine for most projects. You can specify ENCODING 'UTF8' if you want to be explicit.
Create a dedicated user – Instead of using the postgres superuser for your app, create a role with limited privileges: CREATE ROLE myuser WITH LOGIN PASSWORD 'mypass'; then GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;.
Use \l+ for more details – Shows size, encoding, and owner.

Troubleshooting

“psql: FATAL: role “postgres” does not exist” – On some installations (especially Homebrew on macOS), the default superuser is your system username. Try psql -U yourusername or just psql.
“psql: FATAL: database “postgres” does not exist” – This happens if the default database wasn’t created. Connect to any existing database, e.g., psql -U postgres -d template1, then create the postgres database or your own.
“Password authentication failed” – Double-check the password you set during installation. If you forgot it, you may need to reset it by editing the pg_hba.conf file temporarily (see official docs).
“Could not connect to server: Connection refused” – The PostgreSQL service may not be running. Start it using your OS’s service manager (systemctl on Linux, brew services on macOS, or the Windows Services app).
“CREATE DATABASE cannot run inside a transaction block” – This occurs if you’re in a transaction. Just run COMMIT; or ROLLBACK; to exit, then try again.

Frequently Asked Questions

How do I create a database from the command line without entering psql?

Use the createdb utility: createdb -U postgres mydb. It’s a direct command that does the same thing as CREATE DATABASE.

Can I create a database with a specific owner?

Yes, use CREATE DATABASE mydb OWNER myuser;. The owner has full control over that database.

What is the maximum length of a database name in PostgreSQL?

Database names can be up to 63 characters long. Longer names are truncated silently, so keep them under that limit.

How do I delete a database?

Use DROP DATABASE mydb;. Be careful – this permanently removes the database and all its data. You cannot drop a database while connected to it; connect to another database first.

Can I create multiple databases at once?

No, the CREATE DATABASE command creates one database at a time. You can run multiple commands in a script, but each is separate.

Is there a way to create a database if it doesn’t exist?

PostgreSQL doesn’t have a built-in IF NOT EXISTS for databases (unlike tables). You can check existence first with SELECT 1 FROM pg_database WHERE datname = 'mydb'; and then create it conditionally in a script.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.