How to Subtract in PostgreSQL

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:

Subtracting Columns

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

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:

Subtracting Dates

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

Consider a table rentals as follows:

Calculate the duration of rentals:

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

Expected output:

Subtraction with Timestamps

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

Imagine we have a table logins:

Calculate the duration of each session:

SELECT id, logout_time - login_time as session_duration FROM logins;

Expected output:

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:

• How to Format Dates in PostgreSQL
• How to Select Weekdays Only in PostgreSQL