How to Normalize Data in PostgreSQL

Why normalize data in PostgreSQL?

Normalization rescales values so order totals, product prices, or customer spend share a comparable scale. This simplifies outlier detection, model training, and dashboard visuals.

When is normalization useful?

Use it before feeding ML models, during KPI comparisons across time, or when plotting metrics with vastly different units.

How do I apply min-max normalization?

WITH stats AS (
SELECT MIN(total_amount) AS min_val,
MAX(total_amount) AS max_val
FROM Orders
)
SELECT o.id,
(o.total_amount - s.min_val) / NULLIF(s.max_val - s.min_val, 0) AS total_amount_norm
FROM Orders o CROSS JOIN stats s;

The NULLIF guard prevents divide-by-zero if all totals are identical.

How do I compute z-scores?

WITH stats AS (
SELECT AVG(total_amount) AS avg_val,
STDDEV_POP(total_amount) AS std_val
FROM Orders
)
SELECT o.id,
(o.total_amount - s.avg_val) / NULLIF(s.std_val, 0) AS total_amount_z
FROM Orders o CROSS JOIN stats s;

Z-scores center data at 0 with a standard deviation of 1, highlighting outliers.

Can I update the table with normalized values?

ALTER TABLE Orders ADD COLUMN total_amount_norm numeric;

WITH stats AS (
SELECT MIN(total_amount) AS min_val,
MAX(total_amount) AS max_val
FROM Orders
)
UPDATE Orders o
SET total_amount_norm = (o.total_amount - s.min_val) /
NULLIF(s.max_val - s.min_val, 0)
FROM stats s;

Add a generated column for automatic updates in PostgreSQL 12+: ALTER TABLE Orders ADD COLUMN total_amount_norm numeric GENERATED ALWAYS AS ((total_amount - (SELECT MIN(total_amount) FROM Orders)) / NULLIF((SELECT MAX(total_amount) FROM Orders) - (SELECT MIN(total_amount) FROM Orders),0)) STORED;

What is the exact syntax?

Use window functions for one-pass computations:

SELECT id,
(total_amount - MIN(total_amount) OVER ()) /
NULLIF(MAX(total_amount) OVER () - MIN(total_amount) OVER (), 0) AS total_amount_norm
FROM Orders;

Parameters:
• Column to scale (e.g., total_amount)
• Aggregate function (MIN, MAX, AVG, STDDEV_POP)
• NULLIF divisor guard
• Optional PARTITION BY to normalize per customer or month.

Best practices for normalization

Store raw data separately; create views or generated columns for normalized values. Always handle zero variance with NULLIF. Document the scaling method in column comments.

Common mistakes to avoid

Divide-by-zero: Forgetting NULLIF when max = min. Fix: wrap the denominator with NULLIF(expr,0).

Mixed scales: Normalizing per-row instead of over the desired partition. Fix: use OVER () for global or OVER (PARTITION BY) for subgroup scaling.

FAQs

Does normalization change original data?

No. Unless you run an UPDATE, queries leave the raw figures untouched.

Is normalization reversible?

Yes—store min/max or mean/stddev and apply the inverse formula to recover original values.

Can I normalize only recent orders?

Absolutely—add a WHERE clause or normalize inside a date-filtered CTE.