But with this operation you will recreate the whole index. - I have
found out, that the name of the constraint's index is the same as the
constraint, so that I can simply rename the index.
My problem is that I want to "hook up" a new version of existing tables
into my production system.
1) While recomputing the content of the new tables in the background,
they all have a prefix, in my case '_'. So while I am computing, the
application is still using the old tables.
2) Then I will give the old tables a prefix, e.g. '__'
3) Then I remove the prefix of the new tables.
4) Then I can drop the old tables without blocking the application.
Steps 2) and 3) are executed in one transaction and include just
renaming, no computing, so it will not block the application. In
opposite, 1) and 4) take much more time.
Example:
"BEGIN;
ALTER TABLE area RENAME TO __area;
ALTER INDEX area_pkey RENAME TO __area_pkey;
...
ALTER TABLE _area RENAME TO area;
ALTER INDEX _area_pkey RENAME TO area_pkey;
...
COMMIT;
DROP TABLE
__area,
__area_name,
__area_area,
__area_surface,
__area_point;"
Do you know a better solution?
David Fetter schrieb:
> On Thu, Oct 08, 2009 at 08:24:06PM +0200, Andreas Kalsch wrote:
>
>> How do I rename constraints? Renaming columns will not rename constraints.
>>
>
> BEGIN;
> ALTER TABLE foo DROP CONSTRAINT bar;
> ALTER TABLE foo ADD CONSTRAINT bluf...;
> COMMIT;
>
> Cheers,
> David.
>