CODEWITHSUNDEEP
postgresqltransactionscopytruncate
Dimaf
Dimaf

Reputation: 683

Postgres: TRUNCATE and COPY in one transaction

I need to execute following transaction in Postgres 9.4:

BEGIN TRANSACTION;
    TRUNCATE TestTable;
    COPY TestTable FROM '/DATAforTestTable' DELIMITER ',' CSV;
END TRANSACTION;

Users must have read access to "old" data in TestTable on time of executing the transaction without waiting of tansaction end. Is it possible? Or I must do it via coping and renaming tables?

Upvotes: 2

Views: 4627

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125414

From the manual:

TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates on, which blocks all other concurrent operations on the table.

Use delete

begin;
delete from t;

Upvotes: 5

Related Questions