主页 / 问题 / 875791
Accepted
Oleg
Asked:2020-08-31 19:06:16 +0800 CST2020-08-31 19:06:16 +0800 CST

PostgreSQL。按条件插入记录

  • 772

假设我们有一个简单的表：

id | some_fk | value
--------------------
1  | 1       | 3

我像这样将数据插入其中：

insert into table (some_fk, value) values (1, 3) returning id

some_fk如果这样的记录已经存在这样的值，DBMS 如何防止插入记录？

也就是说，在这种情况下，例如values (1, 4)，您可以插入，但values (1, 3)不能，因为已经存在具有此类值的记录。

我无法更改数据库的结构。只对表读写数据。

postgresql

1 个回答

  1. Best Answer

    正确

    添加constraint唯一索引或构建唯一索引。这是两个极为相关的概念，在语法和创建能力上略有不同。

    耙子和自行车

    如果由于某种奇怪的原因无法找到正确的解决方案，那么您可以拄着拐杖跳来跳去，但代价是明显的开销。

    insert into tablename (some_fk, value) 
  select 1, 4 
  where not exists (select 1 from tablename where some_fk = 1 and value = 4)
returning id

    该方法很简单，但由于事务的可见性，或者race condition最终可能给出不正确的结果。因此，有必要在事务中首先在表上获取一个合适的锁。最小拟合是SHARE ROW EXCLUSIVElock，这将使所有希望修改该表数据的并发事务等待：

    begin;
lock table tablename in SHARE ROW EXCLUSIVE mode;
insert into tablename (some_fk, value) 
  select 1, 4 
  where not exists (select 1 from tablename where some_fk = 1 and value = 4)
returning id;
commit;

    在事务期间故意阻止对表的任何更改，而不是仅对冲突事务执行唯一性检查，可以理解地显着降低系统性能。因此，您不应在没有充分理由的情况下使用此方法。

    自行车 #2

    如果您非常幸运，并且您可以控制对该表的所有advisory locks写入操作，那么您可以使用. 这个想法是一样的，但不是一个大的，你lock table请求一个带有某种钥匙的自定义锁。

    begin;
select pg_advisory_xact_lock(
   /* выберите ключ из одного bigint 
      или двух int для этой задачи, 
      имеет смысл в каком-то виде использовать 
      желаемое значение some_fk */
);
insert into tablename (some_fk, value) 
  select 1, 4 
  where not exists (select 1 from tablename where some_fk = 1 and value = 4)
returning id;
commit;

    但同样，这只有在您可以确保只有您将值写入表的情况下才有效。

    如果您无法获得唯一约束、锁定表或保证整个记录仅由您完成，那么您将遇到行存在检查的重复或误报。只是有not exists一部分的请求，也许是大部分，将涵盖，但不会成为担保人。

    • 2

相关问题