重复的密钥插入问题 [英] Duplicate key insert question

问题描述

我有一个包含主要字段和其他一些字段的表。什么是插入到该表中的最快方式？假设有时候我可能会尝试插入具有重复主键的记录并且想要那个

失败了吗？


我知道如果我尝试普通插入这将有效，但在这种情况下

我试图插入一个重复的键，插入失败（因为它应该是
）并记录错误。


我可以先检查一下是否已经存在一个带有

的条目，与我试图插入的那个相同，但这意味着相当多的额外操作。


是否有一种快速而干净的方式来执行以下操作：


INSERT到表值（...）如果还没有pkeys = x

谢谢，


Jean-Christian Imbeault


PS我问的原因是开放源项目使用MySQL

作为他们的数据库，他们有一个PG的端口，不是ery clean b / c DB

代码不断尝试插入重复的主键。根据他们的说法

MySQL不会抱怨并且只是放弃插入而PG（因为

右）抱怨。我已经提出要清理他们的PG插入代码，但是他们说他们不想要太多的额外支票，因为他们的应用程序写了数据给b a
*很多*和任何额外的检查会显着减慢

申请...

---------------- -----------（播出结束）---------------------------

提示4：不要杀死-9''邮政局长

I have a table with a primary field and a few other fields. What is the
fastest way to do an insert into that table assuming that sometimes I
might try to insert a record with a duplicate primary key and want that
to fail?

I know that if I try a plain insert this will work, but in the case
where I am trying to insert a duplicate key, the insert fails (as it
should) and an error is logged.

I could first do a check to see if there is already an entry with the
same key as the one I am trying to insert but that would mean quite a
few extra operations.

Is there a quick and clean way of doing something like:

INSERT into table values(...) IF there isn''t already a row with pkey=x

Thanks,

Jean-Christian Imbeault

PS The reason I am asking is that an open source project is using MySQL
as their DB and they have a port to PG that isn''t very clean b/c the DB
code keeps trying to insert duplicate primary keys. According to them
MySQL doesn''t complain and just drops the insert whereas PG (as is
right) complains. I''ve offered to clean up their PG insertion code but
they say that they don''t want too many extra checks as their app writes
to the DB a *lot* and any extra check is going to slow down the
application noticeably ...
---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster

推荐答案

2003年7月1日星期二08:10下午，Jean-Christian Imbeault写道：

On Tuesday 01 July 2003 08:10 pm, Jean-Christian Imbeault wrote:

我有一个包含主要字段和其他一些字段的表。假设有时候我可能会尝试插入一个带有重复主键的记录，并希望
失败，那么最快的插入表格的方法是什么？
<我知道如果我尝试使用普通插入，这将有效，但是在我试图插入重复键的情况下，插入失败（因为它应该）并且错误是记录。

我可以先检查是否已经有一个条目与我试图插入的那个相同的键，但这意味着相当的
几乎没有额外的操作。

是否有一种快速而干净的方式来执行以下操作：

INSERT到表值（...）如果还没有行与pkey = x

谢谢，

Jean-Christian Imbeault

I have a table with a primary field and a few other fields. What is the
fastest way to do an insert into that table assuming that sometimes I
might try to insert a record with a duplicate primary key and want that
to fail?

I know that if I try a plain insert this will work, but in the case
where I am trying to insert a duplicate key, the insert fails (as it
should) and an error is logged.

I could first do a check to see if there is already an entry with the
same key as the one I am trying to insert but that would mean quite a
few extra operations.

Is there a quick and clean way of doing something like:

INSERT into table values(...) IF there isn''t already a row with pkey=x

Thanks,

Jean-Christian Imbeault

您好，不确定这是否正在回答你的问题，但我在这里问了类似的

问题。我问过使用INSERT WHERE NOT EXISTS（你可以在PostgreSQL中使用
）。您可以这样做：


INSERT INTO mytable

SELECT''value1''，''value2''

什么不存在

（从mytable中选择NULL

WHERE mycondition）


这将在失败时返回0，但确实如此先检查一下。不知道你是否能够真正负担得起b $ b。仅供参考，这里提出了一些讨论

。以下是存档的链接：
http://marc.theaimsgroup.com/?l=post...NOT+EXISTS&q=b

>
希望有所帮助。

RDB

-

Reuben D. Budiardja

物理和天文学

田纳西州诺克斯维尔田纳西大学

----------------------- --------------------------

/" \ ASCII针对HTML的Cookie功能广告

\ /电子邮件和专有格式

X附件。

/ \

----------- --------------------------------------

你有没有被使用过今天是微软吗？

选择你的生活。选择自由。

选择LINUX。

---------------------------- ---------------------

--------------------- ------（广播结束）---------------------------

提示4：唐''''杀'-9''邮政局长

Hi, not sure if this is answering your question, but I just asked similar
questions here. I asked about using INSERT WHERE NOT EXISTS (which you can do
in PostgreSQL). Here is what you can do:

INSERT INTO mytable
SELECT ''value1'', ''value2''
WHERE NOT EXISTS
(SELECT NULL FROM mytable
WHERE mycondition)

This will just return 0 when fails, but it does check first. Don''t know if you
can really afford that. Just for reference, this brought up some discussion
here. Here is a link to the archive:
http://marc.theaimsgroup.com/?l=post...NOT+EXISTS&q=b

Hope that helps.
RDB
--
Reuben D. Budiardja
Department of Physics and Astronomy
The University of Tennessee, Knoxville, TN
-------------------------------------------------
/"\ ASCII Ribbon Campaign against HTML
\ / email and proprietary format
X attachments.
/ \
-------------------------------------------------
Have you been used by Microsoft today?
Choose your life. Choose freedom.
Choose LINUX.
-------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster

2003年7月2日星期三上午09:58:28 +0900，Jean-Christian Imbeault写道：

On Wed, Jul 02, 2003 at 09:58:28AM +0900, Jean-Christian Imbeault wrote:

Alvaro Herrera写道：

Alvaro Herrera wrote:

不，只有第二和第二。一个人会失败（虽然第二个人很困难）

No, only the "second" one will fail (though it''s difficult which one is
the second)

我无法获得工作链接所以我无法理解为什么汤姆把它击落了。
但如果汤姆把这个想法击落......然后它就不能正确。

I couldn''t get the link to work so I couldn''t read why Tom shot it down.
But if Tom shot down this idea down ... then it mustn''t be correct.

线程在这里：
http://groups.google.com/groups?hl = e ... EXISTS％26ie％3D


解决方案不正确，因为有一个竞争条件。

The thread is here:
http://groups.google.com/groups?hl=e...EXISTS%26ie%3D

The solution is not correct in that there _is_ a race condition.

你应该检查插入
函数的返回值，看它是否成功。

You should check the returned value from the insertion
function to see if it succeeded or not.

不，我想要的是什么有一个查询，*总是*插入，如果
没有这个primar的记录如果已存在使用此主键的记录，则y键和*始终*不执行任何操作（不会失败，
不会生成错误）。我不想检查返回值：）

No, what I want if to have one query that will *always* insert if there
is no record with this primary key and *always* do nothing (not fail,
not generate an error) if there is already a record with this primary
key. I don''t want to check return values :)

没办法。


-

Alvaro Herrera（< alvherre [a] dcc.uchile.cl>）

" No es bueno caminar con un hombre muerto"


---------------------------（广播结束）------------------ ---------

提示6：您是否搜索了我们的列表档案？

http://archives.postgresql.org

No way.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No es bueno caminar con un hombre muerto"

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Alvaro Herrera写道：

Alvaro Herrera wrote:

不，只有第二个一个人会失败（虽然第二个人很困难）


来自：
http：//marc.theaimsgroup.com/？l = post ... 6988915991& w = 2


Ian Barwick写道：


[...]


3年前，我提出了同样的解决方案。汤姆把它击落：


[...]


我无法获得工作链接所以我不能阅读为什么汤姆把它击落了。

但是如果汤姆把这个想法击落......那么它就不可能是正确的。

No, only the "second" one will fail (though it''s difficult which one is
the second)
From:

http://marc.theaimsgroup.com/?l=post...6988915991&w=2

Ian Barwick wrote:

[...]

I proposed that same solution 3 years ago. Tom shoots it down:

[...]

I couldn''t get the link to work so I couldn''t read why Tom shot it down.
But if Tom shot down this idea down ... then it mustn''t be correct.

如果我按照线程正确地遵循所有参数那么
是* no *方式来做我（和你;）想要的一个简单查询。

If I followed all the arguments correctly according to the thread there
is *no* way to do what I (and you ;) want in one simple query.

不，没有。

No, there''s not.

你说不，但首先你说建议的方法有效。

建议的方法，如果它是正确的，对我来说足够简单。通过简单的我

意味着所有可以通过一个查询完成。

你应该检查插入
函数的返回值，看它是否成功。

You say no, but at first you say that the proposed method works. The
proposed method, if it is correct, is simple enough for me. By simple I
mean all can be done with one query.
You should check the returned value from the insertion
function to see if it succeeded or not.

不，我想要的是如果有一个查询将*总是*插入如果

没有这个主键和*总是*什么都不做（没有失败，

不会产生错误）如果已经有这个主要

键的记录。我不想查看返回值:)


谢谢，


Jean-Christian Imbeault

---------------------------（广播结束）------------------ ---------

提示4：不要杀死-9''邮政局长

No, what I want if to have one query that will *always* insert if there
is no record with this primary key and *always* do nothing (not fail,
not generate an error) if there is already a record with this primary
key. I don''t want to check return values :)

Thanks,

Jean-Christian Imbeault
---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster

这篇关于重复的密钥插入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！