Re: Denormalization question, history+ current

eric soroos <eric-psql@soroos.net> writes:
> What I currently do from an outside app:

> insert into history_foo (column1, ...) values (bar, ...);
> update current_foo set column1=bar, ... where pkey=a;

> What I'd like to do is insert, then have a trigger/rule assemble an
> update statement for the current_foo table, only updating the columns
> that are non-null and in the current_foo table.

Is it critical (or even desirable?) for your app to control the values
being inserted into the extra columns of the history table?  Or are
those columns well-defined values like timestamps?

The way I'd be inclined to do this is to have the app manipulate
current_foo directly, not touching the history table, and then let
a rule or trigger insert into the history table.

A simple rule would be along the lines of

ON INSERT TO current DO
INSERT INTO history SELECT new.*, current_timestamp, current_user;

You might get better performance with a trigger, however.

            regards, tom lane