How to Set Up a Local PostgreSQL Database for Analytics Practice

Most analytics practice goes wrong in the same quiet way: you learn a syntax, then never touch real data. A local PostgreSQL database fixes that. It is free, it runs on your own machine, and nothing you do to it matters — which is exactly why it is such a good place to learn. You can drop a table by accident, reload it, and try the same join three different ways without anyone asking why the dashboard has broken.

Why run Postgres locally instead of in the cloud?

Cloud warehouses are excellent, but they add friction. You need an account, a billing card and a mental model of permissions before you can write a single query. A local instance skips all of that. Queries return in milliseconds because the dataset is tiny and the server sits on the same machine. You also get the full PostgreSQL feature set — window functions, common table expressions, JSON support — so the syntax you learn transfers directly to managed Postgres services.

The other advantage is repetition. Practising means running the same query until it becomes automatic, and that is much easier when there is nothing to switch on, no cold-start penalty and no cost ticking up in the background.

Installing PostgreSQL on your machine

Pick the route that matches your operating system.

• Windows and macOS: the installer from EnterpriseDB is the least fiddly option. It bundles the server, the psql command-line client and pgAdmin, a graphical tool. During setup you will be asked for a password for the postgres superuser — write it down somewhere you will find it again.
• macOS with Homebrew: brew install postgresql, then brew services start postgresql. Homebrew creates a role matching your system username, so you can usually connect without a password.
• Debian and Ubuntu: sudo apt install postgresql. The service starts automatically, and sudo -u postgres psql gets you in.
• Fedora and RHEL-based systems: sudo dnf install postgresql-server, then initialise the cluster and enable the service.

Once installed, confirm it works from a terminal:

psql --version

Then create a database you are happy to make a mess in:

createdb practice psql -d practice

If createdb complains about a missing role, connect as the superuser instead — psql -U postgres -d postgres — and run CREATE DATABASE practice; from there.

Building a small UK sales table

A realistic practice dataset does not need to be big. It needs to be shaped like the thing you will meet at work: dates, categories, numbers, and enough rows to make aggregation interesting. A UK sales table works well because you already know the regions and can spot a wrong answer by eye.

CREATE TABLE sales ( sale_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, sale_date date NOT NULL, region text NOT NULL, product text NOT NULL, quantity integer NOT NULL CHECK (quantity > 0), unit_price numeric(10,2) NOT NULL CHECK (unit_price >= 0) );

Two details are worth copying deliberately. The CHECK constraints stop nonsense data before it lands, which is a habit worth building early. And numeric(10,2) stores money as a decimal rather than a floating-point number, so totals do not drift by fractions of a penny.

Loading enough rows to be useful

You can type a couple of dozen INSERT statements by hand, and for a first session that is fine. Once you want a few thousand rows, write them to a CSV file and load it:

\copy sales (sale_date, region, product, quantity, unit_price) FROM 'sales.csv' CSV HEADER;

The backslash matters. \copy runs on your machine and streams the file to the server, so it works even when the database lives elsewhere and cannot read your folders. Plain COPY is the server-side version and needs the file to sit where the server can see it.

Your first four analytical queries

Start with a single aggregate, then build up. Each query below adds one new idea.

1. Revenue by region. SELECT region, SUM(quantity * unit_price) AS revenue FROM sales GROUP BY region ORDER BY revenue DESC; Group by a dimension, aggregate a measure, sort. That pattern sits behind most reporting you will ever write.
2. Monthly trend. Use date_trunc('month', sale_date) as the grouping key so every row in January lands in one bucket. Truncating is safer than converting a date to text.
3. A running total. SUM(revenue) OVER (ORDER BY month) gives a cumulative figure. Window functions like this are where PostgreSQL starts to feel powerful, and they are awkward to reproduce in a spreadsheet.
4. Ranking within a group. ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) labels the best-selling products inside each region, which quickly shows that your top product in Scotland is not your top product in London.

Run each one, then change a clause and run it again. The fastest way to internalise SQL is to break your own query on purpose and read the error message carefully.

Habits that keep practice productive

Save every query you write to a file, including the failures. A folder of .sql files, each annotated with the question it answers, becomes a personal reference you will actually return to. Keep a scratch database alongside practice so you can experiment without rebuilding your main table. And when a result surprises you, check the row count first: most "wrong" answers are a join that quietly multiplied rows.

If you would rather not install anything system-wide, Docker runs the same server in a container with a single command, and a container can be deleted and recreated in seconds. It is a good option once the basics feel comfortable, but the direct install is simpler for a first attempt.

Where to take it next

Once the sales table feels familiar, add a second table — a product list with categories and unit costs — and practise joins between the two. Then load a genuinely messy CSV with missing values and inconsistent capitalisation, and clean it using SQL alone. That combination of joining and cleaning is most of the day-to-day work in analytics, and you will have learned it on a database you are free to destroy.

Photo: Pexels / Pixabay