Re: ALTER TABLE ... REPLACE WITH

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> There are various applications where we want to completely replace the
> contents of a table with new/re-calculated data.

> It seems fairly obvious to be able to do this like...
> 1. Prepare new data into "new_table" and build indexes
> 2. Swap old for new
> BEGIN;
> DROP TABLE "old_table";
> ALTER TABLE "new_table" RENAME to "old_table";
> COMMIT;

Why not

BEGIN;
TRUNCATE TABLE;
... load new data ...
COMMIT;

> What I propose is to write a function/command to allow this to be
> explicitly achievable by the server.

> ALTER TABLE "old_table"
> REPLACE WITH "new_table";

I don't think the cost/benefit ratio of this is anywhere near as good
as you seem to think (ie, you're both underestimating the work involved
and overstating the benefit). I'm also noticing a lack of specification
as to trigger behavior, foreign keys, etc. The apparent intention to
disregard FKs entirely is particularly distressing,

regards, tom lane