Creating Databases & Schemas

This is the lesson where everything becomes hands-on. From here onwards every concept you learn will be practiced on a real database with real data. In this lesson you will create a PostgreSQL database, understand what schemas are and why they matter, load the course dataset, and verify everything is working. By the end your environment is fully set up and ready for every lesson ahead.

The Course Dataset — Load It Now

If you have not downloaded the course dataset yet, download it now. Every lesson from this point forward uses it. The dataset is a realistic online store database called Dataplexa Store. It has five tables — customers, products, orders, order_items, and employees — with hundreds of rows of real-looking US-based data covering customers, a product catalogue with prices and categories, orders with dates and statuses, line items, and employee records.

What is a Database in PostgreSQL

A database in PostgreSQL is the top-level container. It holds everything — schemas, tables, views, functions, indexes, and users. When you connect with psql or pgAdmin, you always connect to a specific database. Different databases on the same PostgreSQL server are completely isolated from each other — a query in one database cannot access tables in another database directly.

When PostgreSQL is first installed, three databases exist by default. postgres is the default maintenance database used for administrative connections. template0 and template1 are template databases — when you create a new database, PostgreSQL copies template1 as the starting point. You should never modify template0.

Creating a Database

You create a new database using the CREATE DATABASE command. Database names should be lowercase, use underscores instead of spaces, and be descriptive. Once created, connect to it immediately using \c in psql.

-- Create the course database
CREATE DATABASE dataplexa_store;

-- List all databases to confirm it was created
\l

-- Connect to the new database
\c dataplexa_store

Database Options You Can Set

When creating a database you can specify additional options — the owner, encoding, and locale. For most projects you will not need to change these, but knowing them helps when working in multi-user or international environments.

-- Create a database with specific options
CREATE DATABASE dataplexa_store
  OWNER = postgres           -- who owns this database
  ENCODING = 'UTF8'          -- character encoding (UTF8 supports all languages)
  LC_COLLATE = 'en_US.UTF-8' -- sort order rules
  LC_CTYPE = 'en_US.UTF-8'   -- character classification
  TEMPLATE = template1;      -- which template to copy from

-- Rename a database
ALTER DATABASE dataplexa_store RENAME TO dataplexa_production;

-- Rename it back
ALTER DATABASE dataplexa_production RENAME TO dataplexa_store;

-- Drop (delete) a database — WARNING: this is permanent and cannot be undone
DROP DATABASE IF EXISTS old_database;

What is a Schema

A schema is a namespace inside a database — a way to organise tables, views, functions, and other objects into logical groups. Think of a database as a building and schemas as floors. Each floor organises its own set of rooms (tables). Different floors can have rooms with the same name without any conflict.

Every PostgreSQL database has a default schema called public. When you create a table without specifying a schema, it goes into public automatically. This is perfectly fine for learning and small projects. In larger systems, schemas help you separate concerns — for example, a sales schema for sales tables, a hr schema for employee tables, and an analytics schema for reporting views.

Creating and Using Schemas

-- Create a new schema
CREATE SCHEMA sales;
CREATE SCHEMA hr;
CREATE SCHEMA analytics;

-- List all schemas in the current database
SELECT schema_name FROM information_schema.schemata;

-- Create a table inside a specific schema
-- Format: schema_name.table_name
CREATE TABLE sales.orders (
  id SERIAL PRIMARY KEY,
  amount NUMERIC
);

-- Query a table in a specific schema
SELECT * FROM sales.orders;

-- Set the default schema for your session
-- so you don't need to type the schema name every time
SET search_path TO sales;

The search_path — How PostgreSQL Finds Tables

When you type SELECT * FROM orders without a schema prefix, PostgreSQL looks through the search_path to find the table. By default the search_path is "$user", public — it first looks for a schema matching your username, then falls back to public. If no table named orders is found in any schema on the search_path, you get an error.

-- Check the current search_path
SHOW search_path;

-- Change search_path for the current session
SET search_path TO sales, public;

-- Change it permanently for a user
ALTER ROLE postgres SET search_path TO sales, public;

Loading the Course Dataset

Now that your dataplexa_store database is created, it is time to load the dataset. The \i command in psql runs every SQL statement inside a file. Make sure you are connected to the dataplexa_store database first, then run the command below, replacing the path with wherever you saved the file on your computer.

-- Make sure you are connected to the right database first
\c dataplexa_store

-- Load the dataset — replace the path with your actual file location
-- On Mac/Linux:
\i /Users/yourname/Downloads/dataplexa-store-dataset.sql

-- On Windows:
\i C:/Users/yourname/Downloads/dataplexa-store-dataset.sql

Verifying the Dataset Loaded Correctly

After loading, run these quick checks to confirm all five tables exist and contain data.

-- List all tables in the database
\dt

-- Check row counts for all five tables
SELECT 'customers'   AS table_name, COUNT(*) AS rows FROM customers
UNION ALL
SELECT 'products',   COUNT(*) FROM products
UNION ALL
SELECT 'orders',     COUNT(*) FROM orders
UNION ALL
SELECT 'order_items',COUNT(*) FROM order_items
UNION ALL
SELECT 'employees',  COUNT(*) FROM employees;

If you see those five tables with row counts, your dataset is loaded and ready. Every lesson from here will use this data for practice.

Dropping and Managing Schemas

-- Drop a schema (only works if the schema is empty)
DROP SCHEMA analytics;

-- Drop a schema and everything inside it
DROP SCHEMA analytics CASCADE;

-- Rename a schema
ALTER SCHEMA sales RENAME TO store_sales;

-- Transfer ownership of a schema
ALTER SCHEMA hr OWNER TO another_user;

Lesson Summary

Next up: Every data type PostgreSQL supports — and how to choose the right one.