PostgreSQL Databases

Unlike MongoDB, PostgreSQL requires you to explicitly create a database before using it — there's no "lazy creation on first insert."

Listing Databases

\l

or, using pure SQL:

SELECT datname FROM pg_database;

Creating a Database

CREATE DATABASE shop;

Connecting to a Database

\c shop
-- You are now connected to database "shop" as user "postgres".

Dropping a Database

DROP DATABASE shop;

You can't drop the database you're currently connected to — connect to a different one (like postgres) first:

\c postgres
DROP DATABASE shop;

DROP DATABASE IF EXISTS shop; avoids an error if it's already gone.

Template Databases

Every new database is actually copied from a template. template1 is the default template — any object you create in template1 will appear in every future database you create. template0 is a pristine, unmodifiable fallback.

CREATE DATABASE shop TEMPLATE template0;

Common Errors & Troubleshooting

• ERROR: database "shop" is being accessed by other users — you (or another session) are still connected to it; run \c postgres to switch away, and close other open connections to shop before dropping it.
• ERROR: database "shop" already exists — use CREATE DATABASE IF NOT EXISTS — actually, PostgreSQL doesn't support that clause for databases; check \l first, or wrap the statement in application logic that catches the "already exists" error.
• Forgetting which database you're connected to — run \conninfo or SELECT current_database(); any time you're unsure — accidentally running DROP DATABASE against the wrong one is unrecoverable without a backup.

Key Takeaways

• Databases must be created explicitly with CREATE DATABASE — there's no auto-creation.
• You can't drop a database while connected to it; switch connections first.
• New databases are copies of template1 by default, which is why customizing template1 affects every database created afterward.