是否有可能在Postgres中捕获外键违规 [英] Is it possible to catch a foreign key violation in postgres

问题描述

我正在尝试将数据插入具有外键约束的表中。如果我要插入的行连续出现约束冲突，我想将这些数据删除。

问题是每次我违反约束时postgres都会返回错误。我可能在插入语句中有一些语句，例如不使用外键约束吗？

编辑：

这是我要执行的查询，信息是字典：

  cursor.execute （ INSERT INTO事件（case_number_id，日期，\ 
会话，位置，event_type，工作者，结果））VALUES \ 
（％（id_number）s，％（date）s，％（session） s，\ 
％（location）s，％（event_type）s，％（worker）s，％（result）s）冲突时不显示，信息）
  

出现外键冲突时会出错

解决方案

如果一次只插入一行，则可以创建保存点在插入之前，然后 rollback 插入失败时（或在插入成功后将其释放）。

< s>对于Postgres 9.5或更高版本，您可以使用 插入...在冲突中什么也不做 ，它会按照说明进行操作。您还可以 在冲突的UPDATE SET column = value ...上写入，这将自动将您的插入内容转换为您所在行的更新与之冲突（此功能有时称为 upsert）。

这不起作用，因为OP正在处理外键约束，而不是唯一约束。在这种情况下，您可以最轻松地使用我之前介绍的savepoint方法，但是对于多行它可能会很乏味。如果您需要一次插入多行，则将它们拆分成多个插入语句应该是合理的性能，提供您不是在自动提交模式下工作，所有插入都在一个事务中进行，并且您不会插入很多行。 / p>

有时候，您确实确实需要在单个语句中进行多次插入，因为与数据库通信的往返开销以及在每个插入上具有保存点的成本也很简单高。在这种情况下，存在许多不完善的方法。 最糟糕的可能是建立一个嵌套查询，该查询选择您的数据并将其与另一个表连接，如下所示：

 插入表_A（column_A，column_B，column_C）
 SELECT A_rows。* 
 FROM VALUES（...）AS A_rows（column_A，column_B，column_C）
 JOIN table_B ON A_rows.column_B = table_B.column_B; 
  

I'm trying to insert data into a table which has a foreign key constraint. If there is a constraint violation in a row that I'm inserting, I want to chuck that data away.

The issue is that postgres returns an error every time I violate the constraint. Is it possible for me to have some statement in my insert statement like 'ON FOREIGN KEY CONSTRAINT DO NOTHING'?

EDIT:

This is the query that I'm trying to do, where info is a dict:

cursor.execute("INSERT INTO event (case_number_id, date, \
session, location, event_type, worker, result) VALUES \
(%(id_number)s, %(date)s, %(session)s, \
%(location)s, %(event_type)s, %(worker)s, %(result)s) ON CONFLICT DO NOTHING", info)

It errors out when there is a foreign key violation

解决方案

If you're only inserting a single row at a time, you can create a savepoint before the insert and rollback to it when the insert fails (or release it when the insert succeeds).

For Postgres 9.5 or later, you can use INSERT ... ON CONFLICT DO NOTHING which does what it says. You can also write ON CONFLICT DO UPDATE SET column = value..., which will automagically convert your insert into an update of the row you are conflicting with (this functionality is sometimes called "upsert").

This does not work because OP is dealing with a foreign key constraint rather than a unique constraint. In that case, you can most easily use the savepoint method I described earlier, but for multiple rows it may prove tedious. If you need to insert multiple rows at once, it should be reasonably performant to split them into multiple insert statements, provided you are not working in autocommit mode, all inserts occur in one transaction, and you are not inserting a very large number of rows.

Sometimes, you really do need multiple inserts in a single statement, because the round-trip overhead of talking to your database plus the cost of having savepoints on every insert is simply too high. In this case, there are a number of imperfect approaches. Probably the least bad is to build a nested query which selects your data and joins it against the other table, something like this:

INSERT INTO table_A (column_A, column_B, column_C)
SELECT A_rows.*
FROM VALUES (...) AS A_rows(column_A, column_B, column_C)
JOIN table_B ON A_rows.column_B = table_B.column_B;

这篇关于是否有可能在Postgres中捕获外键违规的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！