"Ottavio Campana" <[EMAIL PROTECTED]> writes: > I need to generate a diff (or something similar) of a table, day by day. > What is the best way to tack insert/update/delete operations? I have two > ideas, and I'd like to hear your opinion: > > 1) pg_dump each day and run diff
You can't use pg_dump directly as the rows are unordered. An update will remove the old row in one place and put the new row possibly in a completely different place. Some operations like CLUSTER or VACUUM FULL could move around rows which doesn't matter to SQL but would show up in a diff. You would have to COPY to a file a query which includes an ORDER BY. > 2) modify some triggers we use and store the information in another table > > I am not aware of any functionality offered by postgresql. Does it exists? Well alternatively you could do the same as 1) but do it in SQL. Something like CREATE TABLE copy_table AS (SELECT * FROM original_table); ... wait a day SELECT * FROM copy_table EXCEPT SELECT * FROM original_table It's not going to be fast though. Probably the triggers are the best option really. They give you more information than a diff in that they tell you when the change occurred, what user made the change, and if multiple changes to the same record occurred you get a record of each. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
