How do I replace a table in Postgres?

Question

Basically I want to do this:

begin;
lock table a;
alter table a rename to b;
alter table a1 rename to a;
drop table b;
commit;

i.e. gain control and replace my old table while no one has access to it.

Can you be more specific regarding where you are stuck or what you cannot seem to be able to achieve? — Fabiano Francesconi, Sep 04 '15 at 20:10
Not so much. I got an error when I tried it at the command line but there may have been an existing table or something that caused it and I misinterpreted it. Therefore you have the best answer. ;-) — user3416742, Sep 04 '15 at 20:24

Erwin Brandstetter · Answer 1 · 2020-12-19T13:11:14.303

19

Simpler:

BEGIN;
DROP TABLE a;
ALTER TABLE a1 RENAME TO a;
COMMIT;

DROP TABLE acquires an ACCESS EXCLUSIVE lock on the table anyway. An explicit LOCK command is no better. And renaming a dead guy is just a waste of time.

You may want to write-lock the old table while preparing the new, to prevent writes in between. Then you'd issue a lock like this earlier in the process:

LOCK TABLE a IN SHARE MODE;

What happens to concurrent transactions trying to access the table? It's not that simple, read this:

Best way to populate a new column in a large table?

Explains why you may have seen error messages like this:

ERROR:  could not open relation with OID 123456

edited Dec 19 '20 at 13:11

answered Sep 04 '15 at 20:34

Erwin Brandstetter

605,456
145
1,078
1,228

14

Also note that neither this nor the original solution will preserve `FOREIGN KEY` constraints referencing the table, views referencing the table, etc. The `DROP` will fail. If you `DROP ... CASCADE` then the referencing views, constraints, etc are dropped, and will not be recreated when you create / rename the replacement table into place. There is not currently a convenient way to "swap" two tables and keep FK constraints etc. – Craig Ringer Sep 05 '15 at 06:32

score 0 · Answer 2 · answered Nov 04 '17 at 05:55

0

Create SQL-backup, make changes you need directly at the backup.sql file and restore database. I used this trick when have added INHERIT for group of tables (Postgres dbms) to remove inherited fields from subtable.

answered Nov 04 '17 at 05:55

Dmitry

105
1
9

score 0 · Answer 3 · answered Oct 06 '21 at 23:08

0

I would use answer#13, but I agree, it will not inherit the constraints, and drop table might fail

line up the relevant constraints first (like from pg_dump --schema-only,
drop the constraints
do the swap per answer#13
apply the constraints (sql snippets from the schema dump)

answered Oct 06 '21 at 23:08

ferenc lazar

11
2

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/30013857) – j__carlson Oct 07 '21 at 00:29

How do I replace a table in Postgres?

3 Answers3

Linked