Subtraction in SQL Query: Postgres Guide 2026

You’re probably staring at a query that should be simple.

Maybe it’s revenue - cost. Maybe it’s “days since signup.” Maybe it’s “rows in yesterday’s snapshot that don’t exist today.” In PostgreSQL, all of those count as subtraction, but they don’t behave the same way, and that’s where people lose time.

A lot of subtraction in sql query tutorials stop at a - b and call it done. That’s fine for arithmetic. It’s not enough when you’re working in Postgres on Supabase, Neon, Railway, Render, Fly, or RDS and you need date math, set subtraction, windowed comparisons, and queries that won’t break on NULL.

Table of Contents

• Fundamental Numeric Subtraction in SQL
  • Start with the plain operator
  • Use calculated columns on purpose
• Mastering Date and Time Subtraction in Postgres
  • Date minus date
  • Timestamp minus timestamp
  • Subtracting intervals and using age
• Subtracting Entire Datasets with EXCEPT
  • EXCEPT for row set subtraction
  • EXCEPT versus LEFT JOIN and NOT IN
• Advanced Subtraction with Window Functions
  • Subtract from a group benchmark
  • Subtract from the previous row with LAG
• Avoiding Pitfalls with NULLs and Data Type Casting
  • NULL makes arithmetic disappear
  • Cast before you subtract
• Frequently Asked Questions about SQL Subtraction
  • Should I use EXCEPT or NOT IN
  • How do I calculate percentage difference
  • Why does Postgres return integer sometimes and interval other times

Fundamental Numeric Subtraction in SQL

Start with the plain operator

If you need a fast win, start here:

SELECT 100 - 35 AS difference;

That - operator is the core subtraction tool in SQL. It became part of ANSI SQL-92 in 1992, and it remains one of the most common operators in analytical work. A Beekeeper Studio write-up on SQL subtraction cites a 2023 Stack Overflow survey where over 70% of analytical SQL queries rely on arithmetic operators.

Here’s a realistic table:

CREATE TABLE products (
  id serial PRIMARY KEY,
  name text,
  price numeric(10,2),
  sale_price numeric(10,2),
  cost numeric(10,2)
);

Now subtract columns directly:

SELECT
  name,
  price,
  sale_price,
  price - sale_price AS discount_amount
FROM products;

And for margin-style math:

SELECT
  name,
  price,
  cost,
  price - cost AS gross_profit
FROM products;

That’s the pattern you’ll use most often. Column minus column. Literal minus literal. Column minus literal.

Use calculated columns on purpose

The mistake I see most often isn’t syntax. It’s unclear naming.

If a subtraction result matters downstream, name it like an output someone can trust:

SELECT
  name,
  actual_sales,
  projected_sales,
  actual_sales - projected_sales AS sales_variance
FROM monthly_sales;

That’s better than calling everything difference. It makes dashboards, exports, and ORM result parsing less annoying.

A few useful patterns:

• Discount amount

  SELECT price - sale_price AS discount_amount
  FROM products;

• Remaining budget

  SELECT allocated_budget - spent_budget AS remaining_budget
  FROM team_budgets;

• Inventory gap

  SELECT counted_stock - expected_stock AS stock_adjustment
  FROM inventory_checks;

Practical rule: If the result has business meaning, alias it with business meaning.

For numeric subtraction in Postgres, prefer numeric when precision matters. That’s especially true for money-like values, discounts, commissions, and tax calculations. Integer subtraction is fine for counts. It’s the wrong tool for values that need decimals.

A compact example with filtering:

SELECT
  id,
  name,
  price - cost AS gross_profit
FROM products
WHERE price - cost > 0;

This works, but in production I usually push the subtraction into a subquery or CTE once the expression starts repeating. It keeps the query readable and avoids copy-paste mistakes.

Mastering Date and Time Subtraction in Postgres

A dashboard says a customer converted in 0 days, but the raw events are eight hours apart. That usually means the query mixed date, timestamp, and timezone assumptions without being explicit.

PostgreSQL handles subtraction well, but it does not return one universal “difference” type. The result depends on the operands. That matters a lot in Supabase, Neon, and any modern Postgres app where the same field might feed SQL filters, API responses, and charts.

Date minus date

Start with the cleanest case:

SELECT
  DATE '2026-01-10' - DATE '2026-01-01' AS days_between;

In PostgreSQL, date - date returns an integer count of days.

That is a good fit for business logic that works in calendar days:

SELECT
  user_id,
  first_purchase_date - signup_date AS days_to_first_purchase
FROM users;

A reporting query often looks like this:

SELECT
  order_id,
  shipped_on - created_on AS fulfillment_days
FROM orders;

If both columns are date, the behavior is simple and predictable. You get 9, not 9 days.

Timestamp minus timestamp

With timestamps, Postgres returns an interval:

SELECT
  TIMESTAMP '2026-01-10 12:00:00' - TIMESTAMP '2026-01-10 09:30:00' AS duration;

That result is useful for session lengths, queue delays, job runtimes, and event gaps:

SELECT
  session_id,
  ended_at - started_at AS session_length
FROM user_sessions;

interval is great for inspection and debugging. It is less convenient when the next step needs a number. Sort thresholds, chart axes, and API payloads often work better with seconds or days.

Convert explicitly:

SELECT
  session_id,
  EXTRACT(EPOCH FROM (ended_at - started_at)) AS duration_seconds
FROM user_sessions;

And for day-based analysis:

SELECT
  user_id,
  EXTRACT(EPOCH FROM (first_purchase_at - signup_at)) / 86400 AS days_to_purchase
FROM users;

If your charting layer requires a number, convert the interval in SQL.

Subtracting intervals and using age

Subtracting an interval from a date or timestamp is a separate pattern:

SELECT now() - interval '7 days' AS seven_days_ago;

That is the normal way to build rolling windows and backfills in Postgres:

SELECT
  id,
  created_at,
  created_at - interval '30 days' AS comparison_start
FROM events;

This keeps the time logic in the query, close to the data, instead of spreading it across application code and cron jobs.

PostgreSQL also gives you age():

SELECT age(now(), created_at) AS record_age
FROM events;

For person-style elapsed time, age() can be easier to read:

SELECT
  customer_id,
  age(first_purchase_at, signup_at) AS time_to_convert
FROM customers;

There is a trade-off here. timestamp - timestamp gives exact elapsed time as an interval. age() expresses a calendar-aware difference in years, months, and days. For billing windows, SLA checks, and anything that needs exact durations, I use direct subtraction. For account age or customer tenure, age() is often the better output.

One gotcha catches a lot of teams. timestamp without time zone and timestamptz do not behave the same way in real systems. If your app serves multiple regions, store and compare timestamps intentionally. In Supabase and Neon projects, that usually means keeping event times in UTC with timestamptz, then converting for display at the edge or in the client.

Subtracting Entire Datasets with EXCEPT

Your nightly sync finishes, and now you need one answer fast: which rows existed in yesterday’s snapshot but are absent now? In PostgreSQL, EXCEPT is the direct way to subtract one result set from another.

SELECT employee_id, name, department
FROM employees_2025

EXCEPT

SELECT employee_id, name, department
FROM employees_2026;

That query returns rows from the first result that do not appear in the second.

For Supabase and Neon projects, this comes up all the time in audit queries, import validation, permission diffs, and snapshot comparisons. If the problem is “A minus B” at the row-set level, EXCEPT usually reads better than rewriting the same intent with joins.

EXCEPT for row set subtraction

A common example is checking which imported records are new:

SELECT email
FROM imported_contacts

EXCEPT

SELECT email
FROM existing_contacts;

That gives you emails present in the import but absent from the main table.

There are two PostgreSQL rules that matter here. EXCEPT compares whole rows, and it returns distinct rows. That means the selected columns must match by count, order, and compatible type.

Good
SELECT id, email FROM a
EXCEPT
SELECT id, email FROM b;

Bad
SELECT id, email FROM a
EXCEPT
SELECT email, id FROM b;

The second query is wrong even though it uses the same column names. PostgreSQL compares column 1 to column 1, column 2 to column 2, in order. If you are diffing snapshots from tables that evolved separately, cast and reorder explicitly instead of relying on implicit alignment.

PostgreSQL also uses EXCEPT where Oracle uses MINUS. The names differ. The set-subtraction behavior is the same idea.

EXCEPT versus LEFT JOIN and NOT IN

You can express “rows in A but not in B” a few ways. The right choice depends on what you need the query to preserve.

Here is the LEFT JOIN version of the earlier query:

SELECT a.employee_id, a.name, a.department
FROM employees_2025 a
LEFT JOIN employees_2026 b
  ON a.employee_id = b.employee_id
WHERE b.employee_id IS NULL;

I use this form when the identity rule is narrower than the selected row. For example, if employee_id defines sameness but other columns may differ, a join makes that explicit. EXCEPT is stricter. It subtracts based on the full projected row.

That distinction matters in real systems. If employees_2025 and employees_2026 contain the same employee_id but a different department, EXCEPT treats them as different rows unless you project only the key:

SELECT employee_id
FROM employees_2025

EXCEPT

SELECT employee_id
FROM employees_2026;

Use EXCEPT when you mean set logic. Use a join when you need record-matching logic.

One last gotcha. If duplicate preservation matters, plain EXCEPT is not enough because it returns distinct rows. PostgreSQL also supports EXCEPT ALL when you want multiset subtraction and need duplicate counts to survive.

Advanced Subtraction with Window Functions

Simple subtraction compares values in the same row. Window functions let you subtract against context.

That’s what you use for analytics queries that answer questions like: how far above category average is this product, or how much did revenue change from the prior month?

Subtract from a group benchmark

Suppose you have product sales by category and want to compare each row to its category average.

SELECT
  product_id,
  category,
  sales,
  sales - AVG(sales) OVER (PARTITION BY category) AS sales_vs_category_avg
FROM product_sales;

That query keeps each original row, computes the average inside its category, and subtracts the benchmark inline.

You can do the same against a category total:

SELECT
  product_id,
  category,
  sales,
  SUM(sales) OVER (PARTITION BY category) - sales AS other_sales_in_category
FROM product_sales;

That’s useful when a dashboard needs “rest of group” logic.

A few patterns where this pays off:

• Store performance: subtract a store’s sales from regional average.
• Feature usage: subtract tenant usage from plan-level baseline.
• Quality checks: subtract an event count from a daily expected count.

Subtract from the previous row with LAG

For trend analysis, LAG() is the workhorse.

SELECT
  month,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS revenue_change
FROM monthly_revenue
ORDER BY month;

That subtracts the prior row’s revenue from the current row.

A partitioned version compares within each account:

SELECT
  account_id,
  month,
  revenue,
  revenue - LAG(revenue) OVER (
    PARTITION BY account_id
    ORDER BY month
  ) AS month_over_month_change
FROM account_revenue;

The first row in each partition has no previous row, so LAG() returns NULL.

That means the subtraction also returns NULL, which is usually correct. If your downstream consumer needs zero instead, handle it explicitly:

SELECT
  account_id,
  month,
  revenue,
  revenue - COALESCE(
    LAG(revenue) OVER (
      PARTITION BY account_id
      ORDER BY month
    ),
    0
  ) AS month_over_month_change
FROM account_revenue;

Use raw NULL when “no prior value exists” is meaningful. Use COALESCE(..., 0) only when the business rule says the first comparison should behave like zero.

Windowed subtraction is where SQL starts replacing application-side loops. If you can express the comparison in one query, keep it in the database. It’s easier to reason about, easier to test, and usually easier to maintain.

Avoiding Pitfalls with NULLs and Data Type Casting

Most broken subtraction queries fail for boring reasons.

Not advanced reasons. Not optimizer reasons. Usually it’s NULL, or a text column pretending to be numeric, or mixing date and timestamp types without being explicit.

NULL makes arithmetic disappear

In SQL arithmetic, NULL propagates.

SELECT 10 - NULL AS result;

That returns NULL, not 10.

Same issue with columns:

SELECT
  actual_sales,
  projected_sales,
  actual_sales - projected_sales AS variance
FROM forecasts;

If either side is NULL, variance becomes NULL.

Use COALESCE() when the business rule says missing values should behave like zero:

SELECT
  actual_sales,
  projected_sales,
  COALESCE(actual_sales, 0) - COALESCE(projected_sales, 0) AS variance
FROM forecasts;

This is the safest default pattern for reports.

Use these rules:

• Financial reports: replace missing numeric values intentionally.
• Event timing: don’t auto-convert missing timestamps unless that’s correct.
• Data quality checks: sometimes a NULL result is the signal you want.

A lot of teams overuse COALESCE. If a missing value means “unknown,” keep it unknown.

Cast before you subtract

Postgres is strict in a good way. It won’t guess forever.

This fails if the column stores text:

SELECT price_text - discount_text
FROM raw_imports;

Cast explicitly:

SELECT
  price_text::numeric - discount_text::numeric AS net_price
FROM raw_imports;

Same idea for date-like strings:

SELECT
  shipped_on_text::date - ordered_on_text::date AS shipping_days
FROM imported_orders;

And when mixing timestamp and date, be deliberate:

SELECT
  delivered_at::date - ordered_at::date AS delivery_days
FROM orders;

or

SELECT
  delivered_at - ordered_at AS delivery_interval
FROM orders;

Those are different queries with different meanings.

A short checklist helps:

When subtraction matters to business logic, explicit beats clever every time.

Frequently Asked Questions about SQL Subtraction

Should I use EXCEPT or NOT IN

Use EXCEPT when you’re subtracting one row set from another and both queries project the same columns.

Use NOT IN only when you’re dealing with a simple value list and you fully understand how NULL in the subquery affects results. In practice, EXCEPT is often safer for set subtraction, especially on messy datasets. Earlier, the verified data noted that PostgreSQL’s EXCEPT can outperform NOT IN on NULL-heavy data.

If you need flexible join conditions or extra selected columns, a LEFT JOIN ... IS NULL pattern can still be the better fit.

How do I calculate percentage difference

Use subtraction first, then divide by the baseline.

SELECT
  actual_price,
  original_price,
  (actual_price - original_price) / NULLIF(original_price, 0) AS percentage_change
FROM prices;

For a discount relative to original price:

SELECT
  price,
  sale_price,
  (price - sale_price) / NULLIF(price, 0) AS discount_ratio
FROM products;

The important piece is NULLIF(..., 0). It prevents division-by-zero errors.

If you want a rounded value, add ROUND() around the final expression after deciding what numeric type you want the output to be.

Why does Postgres return integer sometimes and interval other times

Because the operand types decide the result type.

Quick reference:

• date - date returns integer days
• timestamp - timestamp returns interval
• date - interval returns a temporal value shifted backward
• timestamp - interval returns a shifted timestamp

Examples:

SELECT DATE '2026-02-10' - DATE '2026-02-01';

SELECT TIMESTAMP '2026-02-10 12:00:00' - TIMESTAMP '2026-02-01 09:00:00';

SELECT now() - interval '1 day';

If the result surprises you, inspect the data types first. In PostgreSQL, subtraction is usually doing the right thing. The query just isn’t operating on the types you thought it was.

If you work in Postgres every day on a Mac, Churros is worth a look. It keeps the database front and center with a keyboard-first editor, schema-aware autocomplete, fast record inspection, staged inline edits with SQL preview, and a calm UI that makes daily querying less noisy. It’s especially nice for Supabase, Neon, Railway, Render, Fly, and RDS workflows where you want a native client that feels fast, focused, and safe.