php - 使用 php 在 postgresql 中截断带约束的表

Question

我有一个表格，我可以从 rss-feed 中获取数据。我需要在导入之前截断数据库表，这样我就没有任何不在 rss-feed 中的“旧”条目。

但是当我尝试截断时，出现错误 duplicate key value violates unique constraint

我曾尝试寻找答案，但找不到任何人解释如何使用 php 来完成。

我的旧代码:

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();
}    

这是我得到的错误:

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

我试过:

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();
}    

但是我得到了同样的错误。我应该如何在截断表时禁用关系？

编辑 1:

如果我尝试:

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();
}    

我收到这个错误:

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

Answer 1

包含 DROP STORAGE 和 REUSE STORAGE 只是为了兼容性；这些子句被解析并被忽略

试试

TRUNCATE TABLE loans_bank