pgAdmin & the Practice Database

psql is great for quick work, but many people like to see their databases in a window: a tree of tables on the left, a query editor in the middle and results in a grid. pgAdmin is the free, open-source graphical tool made by the PostgreSQL community for exactly that.

In this lesson you will connect pgAdmin to your server, create the shop practice database from a script, and get to know the five tables that every example in this course uses.

Installing and opening pgAdmin

If you used the EDB installer on Windows or macOS, pgAdmin 4 is already installed; look for it in the Start menu or Applications folder. Otherwise download it from https://www.pgadmin.org. It runs as a desktop app, or as a web application on a server that a whole team opens in the browser.

On first start, pgAdmin may ask you to set a master password. This password protects the server passwords that pgAdmin saves for you. It is not your PostgreSQL password.

Registering a server

pgAdmin does not contain a database; it connects to one. The EDB installer adds your local server for you. For any other server (Homebrew, Linux, Docker or a cloud database), add it yourself:

1. In the Object Explorer tree on the left, right-click Servers and choose Register then Server.
2. On the General tab, type a name you will recognise, such as Local PG 18.
3. On the Connection tab, enter the host name (localhost), port (5432), maintenance database (postgres), user name (postgres) and password.
4. Tick Save password if this is your own computer, then click Save.

Expand the server and you will see Databases, Login/Group Roles and Tablespaces. Inside a database, tables live under Schemas, then public, then Tables.

Creating the shop database

Right-click Databases, choose Create then Database, type shop as the name and click Save. Every dialog in pgAdmin has an SQL tab that shows the command it will run, which is a good way to learn. For this dialog it is simply:

CREATE DATABASE shop;

Now load the data. Download shop.sql. Select the new shop database in the tree, open the Query Tool from the Tools menu, use its Open File button to load shop.sql, and press F5 (or the Execute button) to run it. The Messages tab should report success, and after a refresh the five tables appear under Tables. The same script works on MySQL and SQLite too, because it uses only standard SQL.

If you prefer the terminal, the same two steps are:

createdb -U postgres shop
psql -U postgres -d shop -f shop.sql

createdb is a small program that runs CREATE DATABASE for you, and -f runs every statement in the file.

The five tables

The tables link through matching IDs. orders.customer_id points to a customer, order_items.order_id points to an order, and order_items.product_id points to a product. employees.manager_id points to another employee, which builds the reporting chain. You can check the row counts yourself:

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
UNION ALL SELECT 'employees', count(*) FROM employees;

table_name  | row_count
------------+----------
customers   | 12
products    | 12
orders      | 15
order_items | 22
employees   | 10
(5 rows)

Here is the whole products table. Keep it in mind, because many later results build on it:

SELECT * FROM products ORDER BY product_id;

product_id | product_name                | category    | price    | stock
-----------+-----------------------------+-------------+----------+------
1          | Wireless Mouse              | Accessories | 799.00   | 120
2          | Mechanical Keyboard         | Accessories | 3499.00  | 45
3          | 27-inch Monitor             | Displays    | 15999.00 | 18
4          | USB-C Hub                   | Accessories | 1899.00  | 60
5          | Laptop Stand                | Accessories | 1299.00  | 75
6          | Noise-Cancelling Headphones | Audio       | 8999.00  | 25
7          | Bluetooth Speaker           | Audio       | 2499.00  | 40
8          | HD Webcam                   | Accessories | 2199.00  | 0
9          | 1TB External SSD            | Storage     | 6499.00  | 30
10         | 64GB Pen Drive              | Storage     | 549.00   | 200
11         | Python Basics (Book)        | Books       | 450.00   | 80
12         | Graphics Tablet             | Accessories | 4599.00  | 12
(12 rows)

Prices print with two decimals because the column type is numeric(10,2), an exact decimal type that is right for money. You will learn about it in Numbers, Text & Boolean Types.

Rows that later lessons rely on

Customer 1, Aarav from Rohtak, is the most frequent buyer. Here are his orders and what they contained:

SELECT o.order_id, o.order_date, o.status, p.product_name, oi.quantity
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE o.customer_id = 1
ORDER BY o.order_id, p.product_name;

order_id | order_date | status    | product_name                | quantity
---------+------------+-----------+-----------------------------+---------
101      | 2025-01-05 | delivered | 64GB Pen Drive              | 1
101      | 2025-01-05 | delivered | Wireless Mouse              | 2
104      | 2025-02-02 | delivered | Noise-Cancelling Headphones | 1
115      | 2025-05-20 | pending   | Bluetooth Speaker           | 1
115      | 2025-05-20 | pending   | Laptop Stand                | 1
(5 rows)

Useful pgAdmin features

• View/Edit Data: right-click a table and choose to view all rows in an editable grid. Handy for small fixes, but write UPDATE statements for anything you need to repeat.
• ERD Tool: right-click a database and generate an entity-relationship diagram of its tables and foreign keys.
• Backup and Restore dialogs: these run pg_dump and pg_restore for you, which you will learn in Backup, Restore & Replication.
• Explain: the Query Tool can draw a picture of a query plan, covered in EXPLAIN & EXPLAIN ANALYZE.

Common mistakes

• Running shop.sql in the wrong database. If the Query Tool was opened on postgres, the tables land there. Check the database name in the Query Tool's title before pressing F5.
• Running the script twice. The second run fails with "relation already exists". Drop the database and create it again for a clean start.
• Confusing the master password with the database password. The master password only unlocks pgAdmin's saved passwords.
• Forgetting to refresh. The tree does not update by itself after you create tables with SQL; right-click and choose Refresh.

Key points

• pgAdmin 4 is a free graphical tool that connects to PostgreSQL servers; it does not contain data itself.
• Register a server with its host, port, maintenance database, user and password.
• The Query Tool runs SQL (F5), and every dialog's SQL tab shows the command it generates.
• Create the practice database with CREATE DATABASE shop; and load shop.sql into it.
• The five tables are customers, products, orders, order_items and employees, linked by ID columns.
• In the browser every run starts from a fresh copy of this database.

SELECT product_name, category, stock
FROM products
WHERE stock < 30
ORDER BY stock, product_name;