How to Subtract in PostgreSQL

August 7, 2024 By Matthew Rathbone

PostgreSQL supports the basic arithmetic operators including subtraction (-) for various numeric data types.

Our first example is straightforward: subtracting two integers. This will work across all SQL-based databases, not just PostgreSQL.

SELECT 15 - 5 AS result;

Expected output:

result
10

Subtracting Columns

Now, let’s look at how to subtract one column from another. Consider a sales table sales with the following structure:

id	item	price	discount
1	A	100	20
2	B	200	50

You want to find the final cost after the discount is applied. You can achieve this by subtracting the discount from the price.

SELECT id, item, price - discount AS Final_Price FROM sales;

Expected output:

id	item	final_price
1	A	80
2	B	150

Subtracting Dates

Subtracting dates in PostgreSQL returns the difference between two dates in days.

Consider a table rentals as follows:

id	item	rent_start_date	rent_end_date
1	Bike	2022-03-01	2022-03-10
2	Car	2022-02-20	2022-03-02

Calculate the duration of rentals:

SELECT id, item, rent_end_date - rent_start_date as rental_duration FROM rentals;

Expected output:

id	item	rental_duration
1	Bike	9
2	Car	10

Subtraction with Timestamps

In PostgreSQL, subtracting timestamps gives the interval between two timestamps.

Imagine we have a table logins:

id	login_time	logout_time
1	2022-03-15 08:00:00	2022-03-15 10:30:00
2	2022-03-15 07:30:00	2022-03-15 10:00:00

Calculate the duration of each session:

SELECT id, logout_time - login_time as session_duration FROM logins;

Expected output:

id	session_duration
1	02:30:00
2	02:30:00

Practical Use Cases

Inventory Management

Here is an example of calculating the available stock for each product by subtracting the number of sold items from the total stock.

Assume we have an inventory table structured as follows:

CREATE TABLE inventory (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255),
    total_stock INT,
    sold INT
);

Sample Data:

INSERT INTO inventory (product_name, total_stock, sold) VALUES
('Product A', 100, 50),
('Product B', 200, 180),
('Product C', 150, 140);

Query: Calculating Available Stock

SELECT product_id, product_name, total_stock - sold AS available_stock
FROM inventory;

Expected Output:

 product_id | product_name | available_stock 
------------+--------------+-----------------
          1 | Product A    |              50
          2 | Product B    |              20
          3 | Product C    |              10

In this query, we subtract the sold quantity from the total_stock to get the available_stock for each product.

Calculating Age

The age function in PostgreSQL is used to calculate the difference between two dates, returning the result as a complex interval type that includes years, months, and days.

Here’s a practical use case where you might want to calculate the age of customers in a table.

Suppose you have a customers table with a birthdate column.

SELECT customer_id, birthdate, age(current_date, birthdate) AS age
FROM customers;

Expected Output:

 customer_id | birthdate  |         age          
-------------+------------+----------------------
           1 | 1990-04-25 | 34 years 3 mons 12 days
           2 | 1985-10-12 | 38 years 9 mons 24 days
           3 | 2000-01-01 | 24 years 7 mons  5 days

Conclusion

Subtraction in PostgreSQL is a versatile tool that can be applied to numbers, dates, and intervals.In this article, we’ve explored subtraction operations in PostgreSQL, handling both numbers and dates. Remember to always check data types and ensure compatibility when performing computations. Stay tuned for more in the series as we dig deeper into advanced SQL concepts. Happy coding!

Other articles you may enjoy: