Update a table after insertion to another table in PostgreSQL?

Question

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?

• Erwin Brandstetter: You have a contradicting mix of quoted and unquoted mixed-case identifiers. stackoverflow.com/a/20880247/939860
• Vioh: @ErwinBrandstetter what exactly do you mean when you said "mix of quoted and unquoted mixed-cased identifiers"? I mean the identifiers that I wrote in the above code are unquoted
• Erwin Brandstetter: Identifiers in the code are unquoted, but references in the text are double-quoted. I'll fix that for you ...

Answer 1

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:

• Refresh materalized view incrementally in PostgreSQL

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

• How do I make a trigger to update a column in another table?

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

• Vioh: Thanks for your answer! VIEW sounds like a good solution here. But if I insert a new car object into the Car table, will the DBMS automatically update the person_cars VIEW table? or will I have to do that manually through the UPDATE statement? I guess it is the prior, but I just wanna make sure. Thanks!
• Vioh: Another similar question is how do I insert instances to the person_cars VIEW table? Do I have to do that manually with the INSERT statement? or does Postgre automatically do that for me every time I add a new person to the Person table?
• Erwin Brandstetter: @Vioh: There is no "view table" for a plain view. Materialized views are actual tables that are only updated on demand. Follow the links to the manual.
• Vioh: when you say that "there is no view table", do you mean that the VIEW acts as some kind of 'virtual' table in the database? If that is the case, then does that mean that the number of cars for each person is not actually stored in the database, but is computed by the DBMS every time I query for it through the SELECT statement?
• Erwin Brandstetter: @Vioh: Yes, that's how a view works. If you want to materialize the snapshot, use a materialized view.

Answer 2

You can create a SQL stored procedure that updates the second table (or does arbitrary other work) and then define an INSERT trigger on the first table. If you do this you might want to add a DELETE trigger as well - presumably if you delete a car you'll want to update the counter as well. And you probably want an UPDATE trigger on person so nobody can modify that field directly. sigh.

That's why it's usually better to compute things like this instead of depending on everyone updating all of the tables correctly. You can do a lot with triggers but it can quickly become an unmanageable mess. However if you absolutely have to update the value like this then your best bet is a trigger.