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

Question

>我有一个表，我从rss-feed获取数据。我需要在导入之前截断数据库表，以便我没有任何不在 rss 提要中的"旧"条目。

但是当我尝试截断时，我收到一个错误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