Inventory sample database for PostgreSQL
A ready-to-run PostgreSQL script with stock levels, reorder points and stock value across four warehouses. Tested on PostgreSQL 15.18. Updated 19 November 2026.
Inventory database for PostgreSQL
Each row is one SKU in one warehouse: quantity on hand, reorder point and quantity, unit cost and price, stock value, supplier code, last restock date and a needs_reorder flag you can check with a formula.
This page has it as a PostgreSQL script: one table and 1,000 rows, with column types chosen for PostgreSQL, primary keys. The script drops the table if it exists, creates it and inserts the rows in one transaction, so you can run it again to reset the data. It is the same fictional data, seed 42, as on the inventory dataset page, where it is also available as CSV and Excel.
We loaded this exact file into PostgreSQL 15.18 and ran every question below on it; the answers shown are that output. The same questions, written for SQLite, PostgreSQL, MySQL, SQL Server and DuckDB, return the same results on all five.
Load it
psql
createdb practice
psql -d practice -f inventory-postgresql.sqlpgAdmin or DBeaver
Create a database, open the Query Tool, open the .sql file and run it.
Tables and columns
inventory 1,000 rows
| Column | PostgreSQL type |
|---|---|
| sku | VARCHAR(20) |
| product_name | VARCHAR(40) |
| category | VARCHAR(20) |
| warehouse | VARCHAR(20) |
| quantity_on_hand | INTEGER |
| reorder_point | INTEGER |
| reorder_qty | INTEGER |
| unit_cost | DECIMAL(9,2) |
| unit_price | DECIMAL(9,2) |
| stock_value | DECIMAL(11,2) |
| supplier | VARCHAR(10) |
| last_restock_date | DATE |
| needs_reorder | BOOLEAN |
Practice questions
answers from PostgreSQL 15.18
What is the stock value held in each warehouse?
SQL
SELECT warehouse, COUNT(*) AS skus, SUM(quantity_on_hand) AS units, ROUND(SUM(stock_value), 2) AS stock_value
FROM inventory
GROUP BY warehouse
ORDER BY stock_value DESC, warehouse;Result
| warehouse | skus | units | stock_value |
|---|---|---|---|
| WH1 Newark | 280 | 26911 | 4,470,329.76 |
| WH2 Chicago | 272 | 26014 | 3,685,288.21 |
| WH4 Reno | 224 | 19666 | 3,350,580.77 |
| WH3 Dallas | 224 | 21832 | 3,200,945.35 |
How many SKUs need reordering in each warehouse?
SQL
SELECT warehouse, SUM(CASE WHEN needs_reorder THEN 1 ELSE 0 END) AS needs_reorder
FROM inventory
GROUP BY warehouse
ORDER BY needs_reorder DESC, warehouse;Yes/no columns are real BOOLEAN values, so they can be used directly in WHERE and CASE.
Result
| warehouse | needs_reorder |
|---|---|
| WH4 Reno | 54 |
| WH1 Newark | 52 |
| WH2 Chicago | 48 |
| WH3 Dallas | 46 |
Does needs_reorder agree with quantity_on_hand <= reorder_point? Count the rows where it does not.
SQL
SELECT COUNT(*) AS mismatches
FROM inventory
WHERE (quantity_on_hand <= reorder_point) <> (needs_reorder);Yes/no columns are real BOOLEAN values, so they can be used directly in WHERE and CASE.
Result
| mismatches |
|---|
| 0 |
Which 5 products have the highest margin per unit?
SQL
SELECT sku, product_name, ROUND(unit_price - unit_cost, 2) AS unit_margin
FROM inventory
ORDER BY unit_margin DESC, sku
LIMIT 5;LIMIT n at the end returns the first n rows.
Result
| sku | product_name | unit_margin |
|---|---|---|
| P0056-WH1 | Workgroup Copier | 610.05 |
| P0056-WH1-1 | Workgroup Copier | 610.05 |
| P0056-WH1-2 | Workgroup Copier | 610.05 |
| P0056-WH1-3 | Workgroup Copier | 610.05 |
| P0056-WH1-4 | Workgroup Copier | 610.05 |
What is the average markup percentage by category?
SQL
SELECT category, ROUND(AVG(100 * (unit_price - unit_cost) / unit_cost), 2) AS avg_markup_pct
FROM inventory
GROUP BY category
ORDER BY avg_markup_pct DESC, category;AVG of an integer column returns a numeric, so no cast is needed.
Result
| category | avg_markup_pct |
|---|---|
| Office Supplies | 58.63 |
| Technology | 53.12 |
| Furniture | 48.89 |
How many restocks happened in each month?
SQL
SELECT to_char(last_restock_date, 'YYYY-MM') AS month, COUNT(*) AS restocks
FROM inventory
GROUP BY to_char(last_restock_date, 'YYYY-MM')
ORDER BY month;to_char(date, 'YYYY-MM') formats a date as year-month text.
Result
| month | restocks |
|---|---|
| 2024-01 | 75 |
| 2024-02 | 76 |
| 2024-03 | 89 |
| 2024-04 | 90 |
| 2024-05 | 88 |
| 2024-06 | 74 |
| 2024-07 | 76 |
| 2024-08 | 88 |
| 2024-09 | 70 |
| 2024-10 | 101 |
| 2024-11 | 86 |
| 2024-12 | 87 |
PostgreSQL differences used here
- AVG of an integer column returns a numeric, so no cast is needed.
- Yes/no columns are real BOOLEAN values, so they can be used directly in WHERE and CASE.
- LIMIT n at the end returns the first n rows.
- to_char(date, 'YYYY-MM') formats a date as year-month text.