PostgreSQL Table Update – How to Update a Table After Insertion to Another Table

database-designpostgresqlview

Let's say that my database has 2 tables (Person & Car):

Person (personID, number_of_cars)
Car (carID, owner)

where the owner attribute in Car refers to the personID in the Person table. Now, what I want is that whenever I insert a new car object into the Car table, then the number_of_cars of the owner will be incremented by one. The only solution that I have come up so far is to write the UPDATE statement right after the INSERT statement, something like this:

INSERT INTO Car (carID, owner) VALUES (1, 1);
UPDATE Person SET number_of_cars = number_of_cars + 1 WHERE personID = 1;

However, I don't feel like this is a good way of writing it because then I would have to do the update after every single insert statement. So what is a good way to achieve this in PostgreSQL?

Best Answer

The cleanest solution is to remove the redundant number_of_cars column completely. Your solution (as well as many related ideas floating around) are not safe against concurrent write access.

Instead, create a VIEW (or a MATERIALIZED VIEW to optimize read performance) like:

CREATE VIEW person_cars AS
SELECT *
FROM  (
   SELECT owner AS personid, count(*) AS number_of_cars
   FROM   car 
   GROUP  BY 1
   ) c
JOIN person p USING (personid);

Or you could have a custom materialized view where you only update persons that had actual changes. Related example:

If you insist on your original idea (and concurrent write access is not an issue), you could use a trigger solution. Basic example:

You need to cover all possible changes: INSERT, UPDATE, DELETE on either table. @bgiles added more considerations.

Related Question