PostgreSQL for Journalists: A UK Guide

1. What PostgreSQL is, and when you need it

PostgreSQL (“Postgres”) is a free, open-source relational database that runs as a server on your machine. It speaks standard SQL, like SQLite, but adds a serious query planner, rich indexing, spatial support and the ability for several people to query the same data at once. This guide assumes you know the SQL basics — SELECT, WHERE, GROUP BY, JOIN— and want to work at a larger scale.

Reach for Postgres when a file-based approach starts to hurt: tens of millions of rows, geographic analysis with PostGIS, or a database that will power a live page. If your project fits in one portable SQLite file, stay there. Download Postgres from postgresql.org.

2. Installing Postgres locally

On a Mac, Postgres.app (postgresapp.com) is the quickest start. On Windows or Linux use the official installers from postgresql.org. If you use Docker, one command gives you a disposable server:

# a throwaway Postgres in Docker
docker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres

# create a database, then connect with the psql client
createdb investigations
psql investigations

Inside psql, the meta-commands \dt lists tables and \d tablenamedescribes one. Pair the server with a GUI — DBeaver or pgAdmin — while you find your feet.

3. Loading a CSV with \copy

First define a table whose columns and types match your file, then stream the CSV in. The client-side \copy reads a file from your computer, which is what journalists usually want.

CREATE TABLE price_paid (
  transaction_id text,
  price          integer,
  date_of_transfer date,
  postcode       text,
  town_city      text,
  county         text
);

-- run inside psql: load a CSV that has a header row
\copy price_paid FROM 'price-paid-2024.csv' WITH (FORMAT csv, HEADER true);

If rows fail on a type mismatch, load everything into an all-text staging table first, clean it in SQL, then cast into the typed table. Always run SELECT count(*) afterwards to confirm the total matches the source file.

4. Queries: WHERE, GROUP BY and JOIN

The everyday SQL you know works identically here, just faster on big tables. This aggregates average price by town and joins in a reference table of regions.

SELECT r.region,
       p.town_city,
       count(*)                 AS sales,
       round(avg(p.price))      AS avg_price
FROM price_paid p
JOIN regions r ON r.town_city = p.town_city
WHERE p.date_of_transfer >= '2024-01-01'
GROUP BY r.region, p.town_city
HAVING count(*) > 50
ORDER BY avg_price DESC;

5. Common table expressions (CTEs)

A WITH clause names a query so you can build an analysis in readable steps rather than nested subqueries. Here we total consultancy spend per authority, then keep those above the overall average.

WITH by_authority AS (
  SELECT authority, sum(amount) AS total
  FROM council_spending
  WHERE expense_type ILIKE '%consultancy%'
  GROUP BY authority
)
SELECT authority, total
FROM by_authority
WHERE total > (SELECT avg(total) FROM by_authority)
ORDER BY total DESC;

ILIKEis a Postgres convenience for case-insensitive matching — handy when suppliers spell categories inconsistently.

6. Window functions

Window functions calculate across related rows without collapsing them, so you can rank or run totals while keeping every line. This finds the three largest payments within each authority.

SELECT authority, supplier, amount
FROM (
  SELECT authority, supplier, amount,
         rank() OVER (PARTITION BY authority ORDER BY amount DESC) AS r
  FROM council_spending
) ranked
WHERE r <= 3
ORDER BY authority, amount DESC;

PARTITION BY restarts the ranking for each authority; swap rank() for row_number() or sum() OVER (...) for cumulative totals.

7. Indexing for speed

An index lets Postgres find rows without scanning the whole table. Add one on the columns you filter or join on most, and use EXPLAIN ANALYZE to check the planner is actually using it.

CREATE INDEX idx_price_town ON price_paid (town_city);
CREATE INDEX idx_price_date ON price_paid (date_of_transfer);

-- see whether a query uses the index or scans the table
EXPLAIN ANALYZE
SELECT * FROM price_paid WHERE town_city = 'LEEDS';

Indexes speed up reads but slow down bulk loads, so create them after your initial \copy, not before.

8. Spatial data with PostGIS (a taste)

PostGIS is an extension that teaches Postgres about geometry. Enable it with one command, then you can ask spatial questions — for example, which properties fall inside a given ward boundary.

CREATE EXTENSION postgis;

-- points (properties) that sit inside a ward polygon
SELECT p.postcode, w.ward_name
FROM properties p
JOIN wards w ON ST_Contains(w.geom, p.geom);

That is only a taste: PostGIS covers distance, area, buffers and reprojection. For a single map, QGIS may be faster; for repeatable spatial joins at scale, PostGIS earns its keep.

9. Working through a GUI: DBeaver and pgAdmin

• 1DBeaver (dbeaver.io) is a free cross-platform client that connects to Postgres and many other databases; good for browsing tables and running ad-hoc queries.
• 2pgAdmin (pgadmin.org) is the official Postgres GUI, with a server dashboard, query tool and visual table editors.
• 3Both let you inspect schemas, view a table's contents and export results to CSV without touching the command line.
• 4Keep psql handy anyway: \copy, \dt and \d are quicker than clicking once you know them.
• 5Save your queries in .sql files under version control so the analysis stays reproducible.

10. Red flags

• ⚠Loading a CSV into all-text columns and forgetting to cast, so numbers sort alphabetically and dates will not compare.
• ⚠Confusing server-side COPY (needs the file on the database host) with client-side \copy (reads your local file).
• ⚠Creating indexes before a big bulk load, which slows the load for no benefit.
• ⚠Running DELETE or UPDATE without a WHERE clause, or without a backup, on the live table.
• ⚠Assuming a query is fast because it worked on a sample; test EXPLAIN ANALYZE on the full table.

11. Migration checklist

• I have installed Postgres (Postgres.app, an official installer, or Docker) and created a database.
• I have defined table columns with the correct types, or staged as text and cast afterwards.
• I have loaded with \copy and confirmed the row count with SELECT count(*).
• I have added indexes on the columns I filter and join on, after the initial load.
• I have checked slow queries with EXPLAIN ANALYZE.
• I have saved every query in a .sql file under version control.

Related guides

Frequently asked questions