Does SQL allows addition and subtractions in column?

Question

Suppose, I want to create a table called payment. after that, I want to create three columns in it. selling price, cost price, and then I want to create a profit column like profit = (selling_price - cost_price). Is it possible and if how?

CREATE TABLE payment( selling_price NUMERIC, cost_price NUMERIC NOT NULL, profit = (selling_price - cost_price),/**** 3rd column profit i want to create

Does this answer your question? [Computed / calculated / virtual / derived columns in PostgreSQL](https://stackoverflow.com/questions/8250389/computed-calculated-virtual-derived-columns-in-postgresql) — Martheen, Jul 28 '20 at 06:24

score 0 · Answer 1 · answered Jul 28 '20 at 06:48

As far as I know, the function you said does not exist in old versions of PostgreSQL. Instead you can create trigger for your table.

CREATE TABLE payment(
    selling_price NUMERIC,
    cost_price NUMERIC NOT NULL,
    profit numeric
);

CREATE OR REPLACE FUNCTION trg_function()
  RETURNS trigger AS
$body$
BEGIN
   NEW.profit := NEW.selling_price - NEW.cost_price;       
   RETURN NEW;
END
$body$
LANGUAGE plpgsql;;


CREATE TRIGGER payment_trg
BEFORE INSERT ON payment
FOR EACH ROW
EXECUTE PROCEDURE trg_function();

Here is the fiddle. Original answer is here

Does SQL allows addition and subtractions in column?

1 Answers1