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.

Grinder

Can you be more specific regarding where you are stuck or what you cannot seem to be able to achieve? – Oxidimetry

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. ;-) – Grinder

Then please specify the error you are obtaining. – Oxidimetry

Answer 1

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

Anguilliform

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. – Plantar

Answer 2

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.

Nanna

Answer 3

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)

Nameplate

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Synchronize