I want to replace the entire contents of a table, without affecting any incoming SELECT statements during the process.

The use case is to have a table which stores mailbox information that is regularly extracted, and needs to be stored in a PostgreSQL table. There are many clients using an application that is constantly querying that same table.

Normally, I would do something like (pseudocode incoming)...


But unfortunately the table cannot be read during this process; due to the time it takes INSERT INTO to complete. The table is locked.

In MySQL, I would have used their atomic RENAME TABLE command to avoid these issues...

CREATE TABLE table_new LIKE table; 
INSERT INTO table_new;
RENAME TABLE table TO table_old, table_new TO table; *atomic operation*
DROP TABLE table_old;

How could I achieve this in PostgreSQL?

For the purposes of this question, you can assume I am not using foreign keys.

  • Why do you think that the table cannot be read while inserting rows in it? Truncate table will have immediate effect across all sessions; however, inserts (if done inside a transaction that wraps them all, as your pseudo-code suggests) will not be visible to other sessions until you commit. Other sessions will be able to select from the table, and will see an empty table until you commit.
    – zgguy
    Commented May 6, 2015 at 17:29
  • 2
    @zgguy the TRUNCATE command will acquire an AccessExclusive lock on the table, so no one else will be able to read from the table until that transaction commits or is rolled back. Commented May 6, 2015 at 18:06
  • 2
    If you use delete instead of truncate it will be slower, but without blocking readers. How many rows do you need to delete?
    – user1822
    Commented May 6, 2015 at 19:42
  • @a_horse_with_no_name Usually between 200-300k rows with many varchar columns. The wait time of DELETE and INSERT would be way too long.
    – Clarkey
    Commented May 7, 2015 at 9:49

1 Answer 1


Right, the TRUNCATE TABLE command you are performing "... acquires an ACCESS EXCLUSIVE lock on each table it operates on", so in the first SQL block you posted, any other clients attempting to access the table after that time will be blocked until your INSERT finishes and you COMMIT.

You can use the same workaround as in your MySQL-specific code; Postgres supports roughly the same syntax and will have similar locking behavior. To wit:

-- You probably want to make sure that no one else is
-- INSERT / UPDATE / DELETE'ing from the original table, otherwise
-- those changes may be lost during this switchover process. One way
-- to do that would be via:
CREATE TABLE "table_new" (LIKE "table");
INSERT INTO "table_new" ...;

-- The ALTER TABLE ... RENAME TO command takes an Access Exclusive lock on "table",
-- but these final few statements should be fast.
ALTER TABLE "table" RENAME TO "table_old";
ALTER TABLE "table_new" RENAME TO "table";
DROP TABLE "table_old";


Extra bonus: Postgres actually supports transactional DDL, unlike MySQL, so in case you need to ROLLBACK the above transaction, you may safely do so.

  • I'm going to do some testing on this, thanks for your answer. If I used the LOCK TABLE method that you suggested, would I need to unlock it again before the COMMIT, or will it unlock itself?
    – Clarkey
    Commented May 7, 2015 at 9:51
  • 1
    EDIT: Found the following statment in this documentation: "There is no UNLOCK TABLE command; locks are always released at transaction end."
    – Clarkey
    Commented May 7, 2015 at 13:11
  • 6
    One thing missing here is all of the constraints attached that still belong to _old Commented Sep 7, 2019 at 13:07
  • @Intellix can you elaborate on that? Does it mean that the constraints are simply named per the old table or that they only pertain to the old table (meaning that the constraints are effectively dropped)?
    – maerics
    Commented Mar 7, 2020 at 1:52
  • 1
    The comment before the table creation (-- LOCK TABLE "table" IN ROW EXCLUSIVE mode;) seems to be insuficient to protect from an update/insert into the source table according to the specs. Two ROW EXCLUSIVE locks can be acquired without any conflict (look at Table 13.2 in postgresql.org/docs/10/explicit-locking.html#LOCKING-TABLES). In order to prevent data updates you need at least a SHARE lock.
    – Pilou
    Commented Mar 13, 2020 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.