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)...
BEGIN TRANSACTION TRUNCATE TABLE INSERT INTO COMMIT
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.