PostgreSQL: dynamic row values (?)

Question

Oh helloes!
I have two tables, first one (let's call it NameTable) is preset with a set of values (id, name) and the second one (ListTable) is empty but with same columns.
The question is: How can I insert into ListTable a value that comes from NameTable? So that if I change one name in the NameTable then automagically the values in ListTable are updated aswell.
Is there INSERT for this or does the tables has to be created in some special manner?
Tried browsing the manual but without success :(

Answer 1

Hmmm... I'm a bit confused about exactly what you want to do or why, but here are a couple of pointers towards things you might want to take a look at: table inheritance, triggers and rules.

Table inheritance in postgresql allows a table to share the data of a another table. So, if you add a row to the base table, it won't show up in the inherited table, but if you add a row to the inherited table, it will now show up in both tables and updates in either place will reflect it in both tables.

Triggers allow you to setup code that will be run when insert, update or delete operations happen on a table. This would allow you to add the behavior you describe manually.

Rules allow you to setup a rule that will replace a matching query with an alternative query when a specific condition is met.

If you describe your problem further as in why you want this behavior, it might be easier to suggest the right way to go about things :-)

Answer 2

The suggestion for using INSERT...SELECT is the best method for moving between tables in the same database.

However, there's another way to deal with the auto-update requirement.

It sounds like these are your criteria:

• Table A is defined with columns (x,y)
• (x,y) is unique
• Table B is also defined with columns (x,y)
• Table A is a superset of Table B
• Table B is to be loaded with data from Table A and needs to remain in sync with UPDATEs on Table A.

This is a job for a FOREIGN KEY with the option ON UPDATE CASCADE:

ALTER TABLE B ADD FOREIGN KEY (x,y) REFERENCES A (x,y) ON UPDATE CASCADE;

Now, not only will it auto-update Table B when Table A is updated, table B is protected against containing (x,y) pairs that do not exist in Table A. If you want records to auto-delete from Table B when deleted from Table A, add "ON UPDATE DELETE."