Creating Your First Database

createdb, CREATE DATABASE, encoding, and \c

Before you can store any data, you need a place to put it. CREATE DATABASE creates that place — an empty container with a name and an encoding, waiting for tables.

That choice — encoding — is a decision you make once and live with forever. Getting it right at creation time is far easier than fixing it later.

In this lab you'll stand up inventory_db correctly before a single column exists.

Two Ways to Create a Database

1. Shell utility: createdb

createdb -h localhost -U postgres \
  --encoding=UTF8 \
  --template=template0 \
  inventory_db

createdb is a thin wrapper around CREATE DATABASE. Use it when you're scripting database provisioning from the shell — no psql session required.

2. SQL: CREATE DATABASE

From the shell, open an administrative connection with psql -h localhost -U postgres -d postgres. If already inside psql, use \c postgres postgres instead. Database and SQL role are separate choices: \c postgres changes only the database and keeps your current role. The default student role cannot create databases or drop a database owned by postgres.

CREATE DATABASE inventory_db
  ENCODING 'UTF8'
  TEMPLATE template0;

The TEMPLATE template0 clause is important when specifying an encoding that differs from the server's default. template1 (the normal default template) may have a different encoding baked in, causing a mismatch error. When in doubt, use template0.

Why Encoding Matters

  ENCODING  → which byte sequences are valid text (UTF8 handles every language)

Getting this wrong causes:

Import errors (a client sends UTF-8; the DB expects LATIN1)
Backup restore failures (encoding mismatch between source and target server) Always use UTF8 unless you have a specific reason not to.

Database encoding

Defines how text is stored at the byte level. UTF8 encodes every Unicode character and is the correct choice for almost every new database. LATIN1 only handles Western European characters and causes errors with anything outside that range.

📦 Create the Database from the Shell

Use the createdb command-line utility — without entering psql — to create inventory_db. Explicitly select the administrative postgres role with -U postgres: the default student role has no CREATEDB privilege. Specify UTF8 encoding explicitly.

createdb -h localhost -U postgres --encoding=UTF8 --template=template0 inventory_db

(no output — silence means success)

✅ Verify It Exists with \l

From the shell, connect with psql -h localhost -U postgres -d postgres, then run \l. Check that inventory_db appears with owner postgres, the role used by createdb. Plain psql uses the default SQL role student, which cannot drop this database or create databases. If you already opened psql that way, run \c postgres postgres to select both the maintenance database and the SQL role postgres.

psql -h localhost -U postgres -d postgres

\l

🔌 Connect to It with \c

Inside psql, run \c inventory_db to switch databases while keeping the SQL role selected in the previous step (postgres). The connection message should say database inventory_db and user postgres. Run \conninfo whenever you want to check both.

\c inventory_db

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

🗃️ Confirm the public Schema Was Auto-Created

Run \dn to confirm that PostgreSQL automatically created the public schema inside the new database.

\dn

List of schemas Name | Owner --------+------------------- public | pg_database_owner (1 row)

🗑️ Drop It and Recreate with SQL

Inside psql, run \c postgres postgres before dropping the database. The syntax is \c DATABASE ROLE: the first postgres is the maintenance database; the second is the administrative SQL role.

You must leave inventory_db before dropping it, and run the command as its owner or a superuser. It was created by postgres, so student cannot drop it. \c postgres alone keeps your current role: if you entered with plain psql, you remain student and get ERROR: must be owner of database inventory_db.

Run \conninfo and confirm database postgres, user postgres. Then run DROP DATABASE inventory_db; followed by CREATE DATABASE inventory_db ENCODING 'UTF8' TEMPLATE template0;. Each successful SQL command prints its command name. If the earlier DROP failed, the database still exists; switch roles and retry without resetting the lab.

\c postgres postgres

DROP DATABASE inventory_db;

CREATE DATABASE inventory_db ENCODING 'UTF8' TEMPLATE template0;

\c inventory_db postgres

\conninfo

DROP DATABASE CREATE DATABASE

Lab complete! inventory_db exists with the right settings:

  NAME     : inventory_db
  ENCODING : UTF8
  STATUS   : 🟢 READY FOR TABLES

You know both ways to create a database — the shell utility and the SQL command.

Enable JavaScript to run the live terminal and track your progress.