SQL course · Module 1: Databases and setup · Lesson 3 of 40
Quick answer: Install PostgreSQL from the official download page at postgresql.org (an installer on Windows; an installer, Postgres.app or Homebrew on macOS; apt on Ubuntu). Then create an empty database with createdb -U postgres shop and load the practice script with psql -U postgres -d shop -f shop.sql. Open the database with psql -U postgres -d shop and type \dt to see the four tables: customers, products, orders and order_items.
In this lesson you will learn:
- which PostgreSQL version to install and where to get it for Windows, macOS and Linux
- how to create a database with
createdb - how to load the course’s sample database from a
.sqlfile withpsql -f - the psql commands you will use every day:
\l,\c,\dt,\dand\q
Before you start: Lesson 1, What is a relational database?
Table of Contents
Which PostgreSQL version to install
The PostgreSQL versioning page lists PostgreSQL 18 (18.6) as the newest supported major version. It also announces PostgreSQL 19 RC 1 (October 15, 2026), a release candidate: a test version before the final release. For learning, install the newest final version, PostgreSQL 18 (PostgreSQL versioning policy).
We ran every example on PostgreSQL 16.15, which is supported until November 9, 2028. Everything our practice database uses is in the current PostgreSQL 18 documentation too.
Step 1: Install PostgreSQL
Go to the official page, postgresql.org/download, and pick your operating system. These are the options it lists.
Windows
The download page offers an interactive installer made by EDB. It installs “the PostgreSQL server”, pgAdmin (a graphical tool) and StackBuilder (PostgreSQL for Windows). The EDB install guide adds these points (EDB: Installing PostgreSQL on Windows):
- On the components screen, keep Command Line Tools selected. That is what installs
psql. - You will choose a password for the database superuser,
postgres. Write it down; you need it every time you connect. - The default port is
5432. Keep it. - “By default, the installer doesn’t modify the system path.” Add the
binfolder (for exampleC:\Program Files\PostgreSQL\18\bin) to yourPathenvironment variable, then open a new Command Prompt.
macOS
The download page lists the EDB installer, Postgres.app (“a simple, native macOS app that runs in the menubar”), Homebrew, MacPorts and Fink (PostgreSQL for macOS). With Homebrew, the formula page shows brew install postgresql@18, and brew services start postgresql@18 to start the server now and at login (Homebrew: postgresql@18). Homebrew marks this formula as “keg-only”, so if your terminal cannot find psql afterwards, read the message Homebrew printed at the end of the install.
One difference matters for this lesson. Postgres.app and Homebrew do not create a postgres user. Postgres.app lists your macOS user name as the default user (Postgres.app), and Homebrew sets up the server with initdb, which names the superuser after “the operating-system user running initdb” (PostgreSQL docs: initdb). So with these two, leave out -U postgres in this lesson’s commands. If you keep it, you get FATAL: role "postgres" does not exist.
Ubuntu and Debian Linux
Ubuntu includes PostgreSQL in its own repository, so sudo apt install postgresql installs the version that ships with your Ubuntu release. For the newest version, the PostgreSQL download page explains how to add the PostgreSQL Apt Repository and then run sudo apt install postgresql-18 (PostgreSQL for Ubuntu).
On Linux the server runs as a system user called postgres, and local connections use “peer” authentication by default, which means your Linux user name must match the database user name (PostgreSQL docs: peer authentication). The Ubuntu Server guide connects with sudo -u postgres psql template1 (Ubuntu Server: Install PostgreSQL). So on Linux, put sudo -u postgres in front of the createdb and psql commands in this lesson and leave out -U postgres.
Step 2: Check that psql works
Open a terminal (Command Prompt or PowerShell on Windows) and ask psql for its version:
$ psql --version
psql (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)
You will see your own version number, such as 18.6. If you get “command not found”, the bin folder is not on your PATH (see the Windows notes above).
A note on how we ran the examples: we used Ubuntu 24.04 and a PostgreSQL 16.15 server on the default port, 5432, and typed the commands exactly as shown. Our test server did not ask for a password. Yours may ask for the password you chose during the install.
Step 3: Create the shop database
createdb creates a new, empty database. The docs describe it as “a wrapper around the SQL command CREATE DATABASE” (PostgreSQL docs: createdb). -U postgres means “connect as the user postgres”.
$ createdb -U postgres shop
No output means success. On Windows you will be asked for your password. Run the same command again and PostgreSQL says the database already exists:
$ createdb -U postgres shop
createdb: error: database creation failed: ERROR: database "shop" already exists
That error is harmless. You create the database only once.
Step 4: Save the practice script as shop.sql
Copy the script below into a plain text file called shop.sql, save it in a folder you can find, and open your terminal in that folder. It builds the four tables from Lesson 1 with invented sample data. It is safe to run again: it drops the tables (DROP TABLE IF EXISTS ... CASCADE) and rebuilds them, which resets the data.
-- shop.sql : practice database for the Eteasmasher SQL course
-- https://eteasmasher.com/database-sql-and-mongodb-roadmap-in-2026/
--
-- Written for PostgreSQL (tested on PostgreSQL 16).
-- How to load it (from Lesson 3 of the course):
-- createdb -U postgres shop
-- psql -U postgres -d shop -f shop.sql
--
-- Safe to run again: it drops the four tables and builds them fresh,
-- so any changes you made to the data are thrown away.
-- All names, emails and cities are invented for practice.
SET client_min_messages = warning; -- hide "table does not exist, skipping" notices
BEGIN;
DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS customers CASCADE;
-- 1. customers: one row per person who signed up
CREATE TABLE customers (
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name text NOT NULL,
email text UNIQUE, -- can be NULL (one customer has no email)
city text, -- can be NULL (one customer has no city)
country text NOT NULL,
signup_date date NOT NULL
);
-- 2. products: one row per thing the shop sells
CREATE TABLE products (
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name text NOT NULL,
category text NOT NULL,
price numeric(10,2) NOT NULL CHECK (price >= 0)
);
-- 3. orders: one row per order; each order belongs to one customer
CREATE TABLE orders (
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers (id),
order_date date NOT NULL,
status text NOT NULL
CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'))
);
-- 4. order_items: the lines inside each order (which product, how many, at what price)
CREATE TABLE order_items (
order_id integer NOT NULL REFERENCES orders (id),
product_id integer NOT NULL REFERENCES products (id),
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(10,2) NOT NULL CHECK (unit_price >= 0), -- price paid at the time of the order
PRIMARY KEY (order_id, product_id)
);
-- Rows are inserted without ids, so PostgreSQL numbers them 1, 2, 3 ...
-- in the order shown. The comments show the id each row gets.
INSERT INTO customers (name, email, city, country, signup_date) VALUES
('Ayesha Malik', 'ayesha.malik@example.com', 'Lahore', 'Pakistan', '2025-11-03'), -- id 1
('Lucas Martin', 'lucas.martin@example.com', 'Toronto', 'Canada', '2025-12-14'), -- id 2
('Sofia Rossi', 'sofia.rossi@example.com', 'Milan', 'Italy', '2026-01-05'), -- id 3
('bilal ahmed', 'bilal.ahmed@example.com', 'Karachi', 'Pakistan', '2026-02-20'), -- id 4 (typed in lower case)
('Emma Clarke', NULL, 'London', 'UK', '2026-03-02'), -- id 5 (no email)
('Daniel Weber', 'daniel.weber@example.com', 'Berlin', 'Germany', '2026-04-11'), -- id 6
('Hira Siddiqui', 'hira.siddiqui@example.com', NULL, 'Pakistan', '2026-05-25'), -- id 7 (no city)
('NOAH WILSON', 'noah.wilson@example.com', 'Chicago', 'USA', '2026-06-08'), -- id 8 (typed in upper case)
('Chloe Dubois', 'chloe.dubois@example.com', 'Paris', 'France', '2026-07-19'), -- id 9
('Omar Haddad', 'omar.haddad@example.com', 'Lahore', 'Pakistan', '2026-09-02'); -- id 10 (has no orders)
INSERT INTO products (name, category, price) VALUES
('Wireless Mouse', 'Electronics', 24.99), -- id 1 (was 22.99 before August)
('Mechanical Keyboard', 'Electronics', 89.00), -- id 2
('USB-C Hub', 'Electronics', 39.50), -- id 3
('Laptop Stand', 'Accessories', 45.00), -- id 4
('Desk Lamp', 'Home Office', 32.00), -- id 5
('Notebook Pack', 'Stationery', 12.50), -- id 6
('Gel Pens 10-Pack', 'Stationery', 6.75), -- id 7
('Noise-Cancelling Headphones', 'Electronics', 149.00), -- id 8
('Ergonomic Chair', 'Home Office', 259.00), -- id 9 (never ordered)
('HD Webcam', 'Electronics', 54.90); -- id 10
INSERT INTO orders (customer_id, order_date, status) VALUES
(1, '2026-06-03', 'delivered'), -- id 1
(2, '2026-06-09', 'delivered'), -- id 2
(3, '2026-06-15', 'delivered'), -- id 3
(1, '2026-06-22', 'cancelled'), -- id 4
(4, '2026-07-01', 'delivered'), -- id 5
(5, '2026-07-08', 'delivered'), -- id 6
(8, '2026-07-14', 'delivered'), -- id 7
(2, '2026-07-20', 'delivered'), -- id 8
(6, '2026-07-29', 'delivered'), -- id 9
(7, '2026-08-04', 'delivered'), -- id 10
(1, '2026-08-11', 'delivered'), -- id 11
(9, '2026-08-18', 'cancelled'), -- id 12
(3, '2026-08-25', 'delivered'), -- id 13
(4, '2026-09-02', 'delivered'), -- id 14
(8, '2026-09-02', 'delivered'), -- id 15 (same day as order 14)
(5, '2026-09-17', 'delivered'), -- id 16
(1, '2026-09-29', 'delivered'), -- id 17
(9, '2026-10-06', 'delivered'), -- id 18
(2, '2026-10-14', 'shipped'), -- id 19
(6, '2026-10-21', 'shipped'), -- id 20
(7, '2026-10-27', 'shipped'), -- id 21
(3, '2026-10-30', 'pending'); -- id 22
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 22.99), (1, 6, 2, 12.50),
(2, 2, 1, 89.00), (2, 1, 1, 22.99),
(3, 8, 1, 149.00),
(4, 4, 1, 45.00),
(5, 3, 2, 39.50), (5, 7, 3, 6.75),
(6, 5, 1, 32.00), (6, 6, 4, 12.50),
(7, 10, 1, 54.90), (7, 1, 1, 22.99), (7, 7, 2, 6.75),
(8, 4, 1, 45.00),
(9, 2, 1, 89.00), (9, 3, 1, 39.50),
(10, 6, 3, 12.50), (10, 7, 5, 6.75),
(11, 8, 1, 149.00), (11, 5, 1, 32.00),
(12, 10, 2, 54.90),
(13, 1, 2, 24.99), (13, 3, 1, 39.50),
(14, 2, 1, 89.00), (14, 4, 1, 45.00), (14, 1, 1, 24.99),
(15, 6, 2, 12.50),
(16, 8, 1, 149.00),
(17, 5, 2, 32.00), (17, 7, 4, 6.75),
(18, 10, 1, 54.90), (18, 3, 1, 39.50),
(19, 1, 3, 24.99), (19, 6, 1, 12.50),
(20, 4, 1, 45.00), (20, 5, 1, 32.00),
(21, 2, 1, 89.00), (21, 7, 2, 6.75),
(22, 8, 1, 149.00), (22, 6, 2, 12.50);
COMMIT;
Step 5: Load the script with psql -f
psql -f reads SQL commands from a file and runs them one by one (PostgreSQL docs: psql). -d shop tells psql which database to use.
$ psql -U postgres -d shop -f shop.sql
SET
BEGIN
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 10
INSERT 0 10
INSERT 0 22
INSERT 0 40
COMMIT
Each line is psql’s reply to one command in the file:
SETturns off the “table does not exist, skipping” notices you would get on the first run.BEGINandCOMMITwrap the whole script in one transaction, so it loads completely or not at all. Lesson 19 explains transactions.DROP TABLEandCREATE TABLEappear four times, once per table.INSERT 0 10means 10 rows were inserted. The last number is the row count; you can ignore the 0. So we loaded 10 customers, 10 products, 22 orders and 40 order items.
A second run gave exactly the same output.
If your file is in another folder, give the full path, in quotes if it contains spaces, for example -f "C:\Users\you\SQL course\shop.sql".
Step 6: Explore the database with psql
Now open psql and look around. This is a real session. The text after the prompt (postgres=# or shop=#) is what we typed.
$ psql -U postgres -d postgres
psql (16.15 (Ubuntu 16.15-0ubuntu0.24.04.1))
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
postgres | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | |
shop | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | |
template0 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(4 rows)
postgres=# \c shop
You are now connected to database "shop" as user "postgres".
shop=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | customers | table | postgres
public | order_items | table | postgres
public | orders | table | postgres
public | products | table | postgres
(4 rows)
shop=# \d customers
Table "public.customers"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+----------------------------------
id | integer | | not null | generated by default as identity
name | text | | not null |
email | text | | |
city | text | | |
country | text | | not null |
signup_date | date | | not null |
Indexes:
"customers_pkey" PRIMARY KEY, btree (id)
"customers_email_key" UNIQUE CONSTRAINT, btree (email)
Referenced by:
TABLE "orders" CONSTRAINT "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)
shop=# \d order_items
Table "public.order_items"
Column | Type | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
order_id | integer | | not null |
product_id | integer | | not null |
quantity | integer | | not null |
unit_price | numeric(10,2) | | not null |
Indexes:
"order_items_pkey" PRIMARY KEY, btree (order_id, product_id)
Check constraints:
"order_items_quantity_check" CHECK (quantity > 0)
"order_items_unit_price_check" CHECK (unit_price >= 0::numeric)
Foreign-key constraints:
"order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id)
"order_items_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(id)
shop=# SELECT count(*) FROM customers;
count
-------
10
(1 row)
shop=# SELECT count(*)
shop-# FROM orders;
count
-------
22
(1 row)
shop=# \q
Here is what each command did:
| Command | What it does |
|---|---|
\l | lists all databases on the server; shop is there now |
\c shop | connects to the shop database; the prompt changes to shop=# |
\dt | lists the tables in the current database |
\d customers | describes one table: columns, types, NOT NULL, keys and links |
\q | quits psql |
\d customers shows the primary key (customers_pkey), the UNIQUE rule on email, and the foreign key from orders that points here.
Your \l list may look a little different. The Collate and Ctype values come from your computer’s language settings when the server was set up (PostgreSQL docs: initdb).
Two more details. The =# at the end of the prompt means you are a superuser. The PostgreSQL tutorial says this is “most likely the case if you installed the PostgreSQL instance yourself” (PostgreSQL tutorial: Accessing a Database). And when a query has no semicolon yet, the prompt changes to shop-#. psql is waiting for the rest of the query, as you can see with the second SELECT count(*). A query only runs when psql sees the ;.
Check the row counts
Run this query to confirm the load. You do not need to understand UNION ALL yet; Lesson 15 covers it.
SELECT 'customers' AS table_name, count(*) AS row_count 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;
table_name | row_count
-------------+-----------
customers | 10
products | 10
orders | 22
order_items | 40
(4 rows)
If you see 10, 10, 22 and 40, your practice database is ready for every lesson in this course.
Where this fits
Once the shop database is loaded, you have everything you need for the rest of this course, up to the SQL window functions practice problems. If you also want to query databases from code, the free Python course teaches the Python you need, and the data analyst roadmap and data engineer roadmap show where these skills lead.
Try it yourself
Exercise 1: Describe the products table
Connect to shop and show the columns and keys of the products table. What type is price?
Exercise 2: Count the order lines
Count the rows in order_items.
Exercise 3: Break the data, then reset it
Delete every row from order_items, count the rows, then run \i shop.sql inside psql and count again. (\i runs a file from inside psql, like -f does from the terminal.)
Solutions
Solution 1
shop=# \d products
Table "public.products"
Column | Type | Collation | Nullable | Default
----------+---------------+-----------+----------+----------------------------------
id | integer | | not null | generated by default as identity
name | text | | not null |
category | text | | not null |
price | numeric(10,2) | | not null |
Indexes:
"products_pkey" PRIMARY KEY, btree (id)
Check constraints:
"products_price_check" CHECK (price >= 0::numeric)
Referenced by:
TABLE "order_items" CONSTRAINT "order_items_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(id)
price is numeric(10,2), and the CHECK rule stops negative prices.
Solution 2
SELECT count(*) FROM order_items;
count
-------
40
(1 row)
There are 40 order lines.
Solution 3
shop=# DELETE FROM order_items;
DELETE 40
shop=# SELECT count(*) FROM order_items;
count
-------
0
(1 row)
shop=# \i shop.sql
SET
BEGIN
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 10
INSERT 0 10
INSERT 0 22
INSERT 0 40
COMMIT
shop=# SELECT count(*) FROM order_items;
count
-------
40
(1 row)
shop=# \q
\i shop.sql rebuilt all four tables, so the 40 rows came back. Start psql in the folder that holds shop.sql, or give \i the full path.
Common mistakes
- Typing a terminal command inside psql.
createdbis a program for the terminal, not SQL. Inside psql it fails withERROR: syntax error at or near "createdb". Quit with\qfirst, or use the SQL formCREATE DATABASE shop;. - Forgetting
-d shop.psql -U postgres -f shop.sqlstill works, but it connects to the database namedpostgresand builds the tables there. Then\dtinpostgreslists customers, order_items, orders and products. Always add-d shop. - The server is not running. On Ubuntu psql then reports
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directoryand asks “Is the server running locally and accepting connections on that socket?”. On other systems the message can name a different socket folder, orlocalhostand port 5432 with “Connection refused”. Start the server (the Windows service, Postgres.app orbrew services start) and try again. - A typo in the database name.
psql -U postgres -d shoppfails withFATAL: database "shopp" does not exist. Use\lto see the real names. - File permissions on Linux. When you run psql as the
postgresuser, it may not be allowed to read files in your home folder.sudo -u postgres psql -d shop -f /home/learner/sql-course/shop.sqlthen fails withpsql: error: /home/learner/sql-course/shop.sql: Permission denied. Copy the file somewhere readable, such as/tmp, and run it from there.
Frequently asked questions
What is the default password for the postgres user?
There is no shared default password. On Windows, the installer asks you to choose one during setup, and you use it whenever psql asks. On Ubuntu, the Ubuntu Server guide shows how to set one from psql with ALTER USER postgres with encrypted password 'your_password';.
Can I use pgAdmin instead of psql?
Yes. pgAdmin comes with the EDB installers for Windows and macOS, and it can run SQL too. This course shows psql because its plain-text output is easy to compare with the lesson.
Do I need to run shop.sql again before every lesson?
No. The data stays in the shop database after you close psql or restart your computer. Run shop.sql again only when you want to reset the data, for example after a lesson where you insert, update or delete rows.
Summary
- Download PostgreSQL from postgresql.org/download; PostgreSQL 18 is the newest final version, and these lessons were tested on 16.15.
createdb -U postgres shopcreates the empty practice database once.psql -U postgres -d shop -f shop.sqlloads (or resets) the four tables: 10 customers, 10 products, 22 orders, 40 order items.- Inside psql:
\llists databases,\cconnects,\dtlists tables,\d tabledescribes one,\qquits. - A SQL query runs only after the semicolon; meta-commands that start with a backslash do not need one.
Next lesson
Next: Lesson 4, SQL SELECT Statement: Columns, Aliases, DISTINCT and LIMIT.
Sources
- PostgreSQL downloads
- PostgreSQL for Windows
- PostgreSQL for macOS
- PostgreSQL for Ubuntu
- PostgreSQL versioning policy
- PostgreSQL documentation: psql
- PostgreSQL documentation: createdb
- PostgreSQL tutorial: Creating a Database
- PostgreSQL tutorial: Accessing a Database
- PostgreSQL documentation: initdb
- PostgreSQL documentation: Peer Authentication
- Postgres.app
- EDB: Installing PostgreSQL on Windows
- Homebrew formula: postgresql@18
- Ubuntu Server documentation: Install and configure PostgreSQL





