I have a table A (task, person) in a postgresql database.

I would like to create a live clone of it, table B (task, person, timestamp) which is synchronized with table A using now() as a value for timestamp column.

In other words when a record is written to table A, I would like it also to be written into table B with a timestamp of when it was written saved in the additional column.

Is that possible to do automatically using postgresql server functionality?

CodePudding user response：

If you rename "A" to "B", add a column to "B" with the correct default (now(), I assume), and then create a view named "A" which just selects all columns of "B" except the new column, then things should continue to work as before. Inserts, updates, and deletes on "A" should automatically happen on "B" instead.

Note that only inserts will get the default value applied. updates will not change the value from what was inserted (you could write a trigger to do that). Is is not clear if that is what you want.

If your app tries to apply DDL (such as with migrations) to the table, that will probably go badly. If it just does SELECT/INSERT/UPDATE/DELETE, it should work fine.

CodePudding user response：

I decided to just alter the existing table and add a column to it with a default set to now(). In order to avoid populating existing rows I did it following this advise: https://stackoverflow.com/a/38916347/14296314

I will just have to watch out for the migration process and deal with it when the time comes.

Thanks @jjanes