Josh Berkus <j...@agliodbs.com> writes: > On 03/19/2014 10:37 AM, Alvaro Herrera wrote: >> I wonder about suggesting other versions of ALTER TABLE that can do >> heap rewrites.
> I don't want to recommend any version of ALTER TABLE until someone > actually tests it on a corrupted database. A test would be a good idea, yes. But in principle it ought to work. > What about simply CREATE TABLE AS SELECT? Presumably that kind of > manual cleanup would work, no? Well, it would leave you with a whole lot of error-prone manual cleanup to do, like repointing foreign key linkages, remaking indexes, etc. And what's possibly more relevant to this discussion, there's no very strong reason to believe that it'd result in data that's any cleaner than the ALTER TABLE way. Note that if you've already suffered some of the potential indirect consequences, like duplicate/missing keys, then there isn't going to be any automatic fix; you're gonna have to clean up the data by hand. But assuming that that hasn't happened, any seqscan-based data extraction ought to do the trick; and ALTER TABLE (as long as you avoid the no-op transformation pitfall) should be as good as other ways, with a lot less risk of human error than a manual recipe. regards, tom lane -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers