Skip to content
SQL

Install PostgreSQL and Load a Sample Database for SQL Practice

Ihsan Ullah · Updated · 17 min read
Install PostgreSQL cover for Lesson 3 of the free SQL course: set up psql and load the sample database

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 .sql file with psql -f
  • the psql commands you will use every day: \l, \c, \dt, \d and \q

Before you start: Lesson 1, What is a relational database?

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 bin folder (for example C:\Program Files\PostgreSQL\18\bin) to your Path environment 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:

  • SET turns off the “table does not exist, skipping” notices you would get on the first run.
  • BEGIN and COMMIT wrap the whole script in one transaction, so it loads completely or not at all. Lesson 19 explains transactions.
  • DROP TABLE and CREATE TABLE appear four times, once per table.
  • INSERT 0 10 means 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:

CommandWhat it does
\llists all databases on the server; shop is there now
\c shopconnects to the shop database; the prompt changes to shop=#
\dtlists the tables in the current database
\d customersdescribes one table: columns, types, NOT NULL, keys and links
\qquits 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. createdb is a program for the terminal, not SQL. Inside psql it fails with ERROR: syntax error at or near "createdb". Quit with \q first, or use the SQL form CREATE DATABASE shop;.
  • Forgetting -d shop. psql -U postgres -f shop.sql still works, but it connects to the database named postgres and builds the tables there. Then \dt in postgres lists 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 directory and asks “Is the server running locally and accepting connections on that socket?”. On other systems the message can name a different socket folder, or localhost and port 5432 with “Connection refused”. Start the server (the Windows service, Postgres.app or brew services start) and try again.
  • A typo in the database name. psql -U postgres -d shopp fails with FATAL: database "shopp" does not exist. Use \l to see the real names.
  • File permissions on Linux. When you run psql as the postgres user, it may not be allowed to read files in your home folder. sudo -u postgres psql -d shop -f /home/learner/sql-course/shop.sql then fails with psql: 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 shop creates the empty practice database once.
  • psql -U postgres -d shop -f shop.sql loads (or resets) the four tables: 10 customers, 10 products, 22 orders, 40 order items.
  • Inside psql: \l lists databases, \c connects, \dt lists tables, \d table describes one, \q quits.
  • 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

Ihsan Ullah

Written & tested by Ihsan Ullah

Data analyst with 3 years of experience and a Bachelor's degree in Computer Science. I write clear, practical guides on data, SQL, Python and AI.

More about me →

Leave a comment

Your email address will not be published. Required fields are marked *