Truncate table with constraint in postgresql with php

Question

I have a table where I get data from a rss-feed. I need to truncate the database table before importing, so that I don't have any "old" entries which is not in the rss-feed.

But when I try to truncate, I get an error duplicate key value violates unique constraint

I have tried searching for an answer but I can't find anyone that explains how to do it with php.

My old code:

try {
    $db->beginTransaction();
    $stmt = $db->prepare("TRUNCATE TABLE loans_bank REUSE STORAGE");
    $stmt->execute();
    
    $db->commit();
} catch(PDOException $ex) {
    //Something went wrong rollback!
    $db->rollBack();
    echo $ex->getMessage();
}

This is the error I get:

SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "REUSE"
LINE 1: TRUNCATE TABLE loans_bank REUSE STORAGE
                                      ^PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "loans_bank_pkey"
DETAIL:  Key (id)=(358976) already exists.' in /home/USER/import/get_bank.php:66
Stack trace:
#0 /home/USER/import/get_bank.php(66): PDOStatement->execute(Array)
#1 {main}
  thrown in /home/USER/import/get_bank.php on line 66

I tried:

try {
    $db->beginTransaction();
    $stmt = $db->prepare("ALTER TABLE loans_bank DISABLE CONSTRAINT loans_bank_pkey");
    $stmt = $db->prepare("TRUNCATE TABLE loans_bank REUSE STORAGE");
    $stmt = $db->prepare("ALTER TABLE loans_bank ENABLE CONSTRAINT loans_bank_pkey");
    $stmt->execute();
    
    
    $db->commit();
} catch(PDOException $ex) {
    //Something went wrong rollback!
    $db->rollBack();
    echo $ex->getMessage();
}

But I get the same error. How should I disable the relationship while I'm truncating the table?

Edit 1:

If I try:

try {
    $db->beginTransaction();
    $stmt = $db->prepare("TRUNCATE TABLE $table");
    $stmt->execute();
    
    
    $db->commit();
} catch(PDOException $ex) {
    //Something went wrong rollback!
    $db->rollBack();
    echo $ex->getMessage();
}

I get this error:

SQLSTATE[0A000]: Feature not supported: 7 ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "loans_loan500" references "loans_bank".
HINT:  Truncate table "loans_loan500" at the same time, or use TRUNCATE ... CASCADE.PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "loans_bank_pkey"
DETAIL:  Key (id)=(358976) already exists.' in /home/USER/import/get_bank.php:69
Stack trace:
#0 /home/USER/import/get_bank.php(69): PDOStatement->execute(Array)
#1 {main}
  thrown in /home/USER/import/get_bank.php on line 69

But what happens to the other tables which is connected, will it be deleted as well if I use CASCADE? — Tomas Jacobsen, Mar 04 '14 at 13:29
Records will be deleted just like `DELETE CASCADE`, based on constraints and relationships. — Digital Chris, Mar 04 '14 at 13:32
The other table won't be deleted, only the entries with matching keys in your loans_bank table, which should be ok if you set up your tables correctly — Loopo, Mar 04 '14 at 13:34
But I don't want that! I have other tables with different loan products, if I do CASCADE all my products will be removed as well! Is there not any way to just temporary disable the relationship? — Tomas Jacobsen, Mar 04 '14 at 13:36

score 0 · Answer 1 · answered Mar 04 '14 at 13:25

0

DROP STORAGE and REUSE STORAGE are included for compatibility only; the clauses are parsed and ignored

Try with

TRUNCATE TABLE loans_bank

answered Mar 04 '14 at 13:25

esdebon

2,460
5
26
33

Updated question with the error I get without `REUSE STORAGE` – Tomas Jacobsen Mar 04 '14 at 13:30

Truncate table with constraint in postgresql with php

Edit 1:

1 Answers1