Creating Your First Database

Objectives

By the end of this lesson, you should be able to:

• Create a new database
• Connect to it
• Explain what a new database starts with automatically

💡 Why this matters: Everything else in this course happens inside a database you create yourself. This lesson creates it, the bookstore database used throughout the rest of this course.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

Creating a Database

CREATE DATABASE bookstore;

That’s the entire statement. Run it from psql (connected to any existing database, postgres is fine) or from pgAdmin’s Query Tool, and a new, empty database named bookstore exists on your server.

SELECT datname FROM pg_database;

  datname
-----------
 postgres
 bookstore
 template1
 template0

bookstore now shows up alongside PostgreSQL’s own default databases. \l in psql shows the same list, and in pgAdmin, right-clicking Databases and choosing Refresh shows bookstore in the tree.

Connecting to It

A fresh connection (via psql -U postgres or pgAdmin) connects to a specific database, not all of them at once. To switch into bookstore from psql:

\c bookstore

You are now connected to database "bookstore" as user "postgres".
bookstore=#

Notice the prompt itself changes, from postgres=# to bookstore=#, confirming exactly which database you’re working in. In pgAdmin, this is simply a matter of clicking bookstore in the tree, then opening a Query Tool against it, everything you run there applies to bookstore, not postgres.

What a New Database Starts With

A brand-new database isn’t completely empty in the structural sense, PostgreSQL automatically creates a schema named public inside it (the same default schema mentioned briefly in an earlier lesson), ready for tables to be created in. This is why CREATE TABLE (starting next module) works immediately after creating a database, with no extra setup step in between.

Trying to create a database that already exists fails, the same way a duplicate CREATE TABLE does:

CREATE DATABASE bookstore;

ERROR: database "bookstore" already exists

Try It

1. Create a database named bookstore.
2. Confirm it exists, either with \l in psql, a query against pg_database, or by finding it in pgAdmin’s tree.
3. Connect into it (\c bookstore in psql, or select it in pgAdmin), and confirm your prompt or selected database changed.
4. Try creating bookstore a second time, and confirm you get an error.

Recap

• CREATE DATABASE name; creates a new, empty database on your server.
• A connection is always to one specific database at a time, \c dbname switches which one psql is connected to.
• A new database automatically gets a public schema, ready for tables, no extra setup required.

Next lesson: this module’s exercises, practicing everything from “what is data” through creating your own database.