ShopLite project 4: ShopLite on PostgreSQL
60 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)Moving a database to another server is mostly a translation of the CREATE script; the data and most queries move unchanged, and the real work is finding the rules the old database gave you for free.
What you'll build
You move ShopLite to PostgreSQL 16. postgresql/schema-pg.sql is the PostgreSQL version of the CREATE part of shoplite.sql: identity columns, TIMESTAMP, ~ for the PIN code check, and three things MySQL did silently that PostgreSQL needs written down: a case-insensitive unique e-mail, indexes on foreign key columns, and (a PostgreSQL bonus) at most one default address per customer. The INSERT statements from project 2 go into postgresql/seed.sql unchanged, and postgresql/reports-pg.sql is reports.sql with three small changes.
The report numbers must match MySQL exactly, which proves the move lost nothing. You finish with a short MySQL versus PostgreSQL comparison in the README, written from what you actually had to change.
$ psql -U postgres -d shoplite -q -f schema-pg.sql
tablename | indexname
-------------+------------------------------
addresses | addresses_pkey
addresses | idx_addresses_customer
addresses | uq_addresses_one_default
categories | categories_pkey
categories | uq_categories_name
customers | customers_pkey
customers | uq_customers_email
order_items | idx_order_items_product
order_items | order_items_pkey
order_items | uq_order_items_order_product
orders | idx_orders_address
orders | idx_orders_customer
orders | orders_pkey
products | idx_products_category
products | products_pkey
products | uq_products_sku
(16 rows)
$ psql -U postgres -d shoplite -q -f seed.sql
$ psql -U postgres -d shoplite -q -f reports-pg.sql
month | orders | units | revenue | running_total | change_vs_last
---------+--------+-------+-----------+---------------+----------------
2026-07 | 4 | 11 | 89081.00 | 89081.00 |
2026-08 | 5 | 12 | 78479.00 | 167560.00 | -10602.00
2026-09 | 5 | 11 | 138083.00 | 305643.00 | 59604.00
(3 rows)
rnk | full_name | orders | spent | avg_order
-----+-------------+--------+-----------+-----------
1 | Arjun Mehta | 2 | 107980.00 | 53990.00
2 | Kabir Singh | 2 | 92098.00 | 46049.00
3 | Asha Rao | 3 | 69334.00 | 23111.33
(3 rows)
main_category | units | revenue
---------------+-------+-----------
Electronics | 16 | 291308.00
Fashion | 6 | 8594.00
Books | 12 | 5741.00
(3 rows)
category | product | units
-------------+-----------------------+-------
Accessories | boAt Airdopes 141 | 5
Books | Atomic Habits | 7
Fashion | Cotton Kurta | 4
Laptops | Lenovo IdeaPad Slim 3 | 2
Mobiles | Redmi Note 13 5G | 2
(5 rows)
city | orders | revenue
-----------+--------+-----------
Mumbai | 2 | 107980.00
New Delhi | 2 | 92098.00
Bengaluru | 3 | 69334.00
Chennai | 4 | 25142.00
Kochi | 2 | 5893.00
Pune | 1 | 5196.00
(6 rows)
sku | name | stock
-----------+----------------------------+-------
ACC-M331 | Logitech M331 Silent Mouse | 0
LAP-HPV15 | HP Victus 15 Gaming | 5
MOB-IP15 | iPhone 15 128GB | 8
(3 rows)
full_name | email | joined_on
------------+------------------------+------------
Imran Khan | imran.khan@example.com | 2026-09-01
(1 row)Requirements
- 1
schema-pg.sqlruns in an existingshoplitedatabase, can be run again, and ends by listing every index. - 2Ids are
GENERATED ALWAYS AS IDENTITY; times areTIMESTAMP; money stays exact withNUMERIC(10,2); every constraint keeps its MySQL name. - 3E-mails are unique whatever the case, through a unique index on
LOWER(email). - 4Every foreign key column used for joins has an index, and a customer can have at most one default address.
- 5
seed.sqlis the INSERT part ofshoplite.sql, copied without a single change, and loads 8, 9, 7, 15, 16 and 28 rows. - 6
reports-pg.sqlprints the same seven reports with the same numbers as MySQL. - 7The README lists what changed between MySQL and PostgreSQL and why.
Milestones
0/5 done · commit and push after each oneTranslate the CREATE script
All six tables exist in PostgreSQL and
\dtlists them.Add the rules MySQL gave you for free
Asha.Rao@Example.comis refused as a duplicate, and the index list shows the foreign key indexes.Load the same seed data
seed.sqlloads 8 customers, 9 addresses, 7 categories, 15 products, 16 orders and 28 items with no change.Run the business reports on PostgreSQL
All seven reports print the same numbers as in MySQL.
Write the MySQL versus PostgreSQL comparison
The README has a short list of every change you made, each with a one-line reason.
Build it in your own GitHub repository and push after each milestone. When everything works, mark the project as complete.
Extra challenges (optional)
Do any you like, each on its own branch, so your main project keeps working.
Try to give Asha a second default address and record the error the partial unique index gives.· easy
Replace the status
VARCHARandCHECKwith a PostgreSQL enum type, then write down one advantage and one drawback.· mediumMake the monthly report show every month from July to October 2026, with 0 for a month without sales.· hard
Solution
Opens when you add your repository link.
Finish up
Mark this lesson complete
This is the last lesson in this section.