2

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?

  • You have a contradicting mix of quoted and unquoted mixed-case identifiers. stackoverflow.com/a/20880247/939860 – Erwin Brandstetter Oct 29 '15 at 17:34
  • @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 – Vioh Oct 29 '15 at 18:03
  • Identifiers in the code are unquoted, but references in the text are double-quoted. I'll fix that for you ... – Erwin Brandstetter Oct 29 '15 at 21:47
3

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.

  • 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 Oct 30 '15 at 4:48
  • 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? – Vioh Oct 30 '15 at 4:57
  • 1
    @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. – Erwin Brandstetter Oct 30 '15 at 5:41
  • 1
    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? – Vioh Oct 30 '15 at 10:27
  • 1
    @Vioh: Yes, that's how a view works. If you want to materialize the snapshot, use a materialized view. – Erwin Brandstetter Oct 30 '15 at 13:31
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.