最常用的“更新或插入”方式？ [英] most idiomatic way to "update or insert"?

问题描述

所以我有一些数据要放到表中。如果

行已经存在（由主键定义），我会想要更新该行的
。否则，我想在行中插入

。


我一直在做类似


的事情从foo中删除name =''xx'';

插入foo值（''xx''，1,2，...）;


但我一直想知道是否有更多的惯用语或规范

的方式来做这件事。


很多TIA，

马克


-

马克哈里森

皮克斯动画工作室


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

提示7：别忘了增加你的免费空间地图设置

So I have some data that I want to put into a table. If the
row already exists (as defined by the primary key), I would
like to update the row. Otherwise, I would like to insert
the row.

I''ve been doing something like

delete from foo where name = ''xx'';
insert into foo values(''xx'',1,2,...);

but I''ve been wondering if there''s a more idiomatic or canonical
way to do this.

Many TIA,
Mark

--
Mark Harrison
Pixar Animation Studios

---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings

推荐答案

Mark，

这不是规范，但我所做的是：


更新foo set thing =''stuff''其中name ='''xx''和thing<>''stuff'';

插入foo（name，thing）（选择''xx''as名字，''东西''作为事情其中

不存在（从foo中选择1，其中name =''xx''）;


我相信如果你把它们放在同一条线上它将是单一的

交易。如果没有真正的变化，它的好处就是不更新行。如果名字被编入索引，它也很快。


谢谢，

彼得达利


- ----原帖-----

来自： pg ***************** @ postgresql.org

[mailto：pg ********** ******* @ postgresql.org]代表Mark Harrison

发送时间：2004年8月4日星期三下午4:26

收件人： pg *********** @ postgresql.org

主题：[GENERAL]最常用的更新或插入方式？

所以我有一些数据要放到表中。如果

行已经存在（由主键定义），我会想要更新该行的
。否则，我想在行中插入

。


我一直在做类似


的事情从foo中删除name =''xx'';

插入foo值（''xx''，1,2，...）;


但我一直想知道是否有更多的惯用语或规范

的方式来做这件事。


很多TIA，

马克


-

马克哈里森

皮克斯动画工作室


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

提示7：别忘了增加你的免费空间地图设置

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

提示7：别忘了增加免费空间地图设置

Mark,
It''s not canonical by any means, but what I do is:

update foo set thing=''stuff'' where name = ''xx'' and thing<>''stuff'';
insert into foo (name, thing) (select ''xx'' as name, ''stuff'' as thing where
not exists (select 1 from foo where name=''xx''));

I believe if you put these on the same line it will be a single
transaction. It has the benefit of not updating the row if there aren''t
real changes. It''s plenty quick too, if name is indexed.

Thanks,
Peter Darley

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org]On Behalf Of Mark Harrison
Sent: Wednesday, August 04, 2004 4:26 PM
To: pg***********@postgresql.org
Subject: [GENERAL] most idiomatic way to "update or insert"?
So I have some data that I want to put into a table. If the
row already exists (as defined by the primary key), I would
like to update the row. Otherwise, I would like to insert
the row.

I''ve been doing something like

delete from foo where name = ''xx'';
insert into foo values(''xx'',1,2,...);

but I''ve been wondering if there''s a more idiomatic or canonical
way to do this.

Many TIA,
Mark

--
Mark Harrison
Pixar Animation Studios

---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings
---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings

我会提到我经常做你正在做的事情。我发现删除所有

现有记录，然后插入我希望看起来更清洁的东西，而不是处理可能出现的各种情况，如果你不这样做的话。 />

这种情况​​最常发生在我有一个项目列表并且有一个UI允许用户编辑整个列表并在一个列表中提交一个全新的列表时动作。

简单地删除旧列表并在单个查询中插入整个新列表

比尝试找出要删除的行要容易得多以及

插入。


-

greg

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

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

I''ll mention that often I do exactly what you''re doing. I find deleting all
existing records and then inserting what I want to appear to be cleaner than
handling the various cases that can arise if you don''t.

This happens most often when I have a list of items and have a UI that allows
the user to edit the entire list and commit a whole new list in one action.
It''s much easier to simply delete the old list and insert the entire new list
in a single query than to try to figure out which rows to delete and which to
insert.

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

我不喜欢t认为有效 - 如果你没有做任何比赛条件

锁定。


为什么：

之前插入行的事务已提交，其他事务

不知道插入的行，所以select不返回任何行。


所以：

你可以创建一个唯一索引和catch插入重复失败。


或：

锁定相关表，然后执行select ... update / insert或insert

....选择，或者你想做什么。


或：

两者。


自己测试一下。


2004年8月5日上午07:51，Peter Darley写道：

I don''t think that works - there''s a race condition if you do not do any
locking.

Why:
Before a transaction that inserts rows is committed, other transactions are
not aware of the inserted rows, so the select returns no rows.

So:
You can either create a unique index and catch insert duplicate failures.

Or:
lock the relevant tables, then do the select ... update/insert or insert
.... select , or whatever it is you want to do.

Or:
both.

Test it out yourself.

At 07:51 AM 8/5/2004, Peter Darley wrote:

Mark，
更新foo set thing =''stuff''其中name ='''xx''和thing<>' 'stuff'';
插入foo（名称，东西）（选择''xx''作为名称，''stuff''作为
不存在的东西（从foo中选择1，其中name =' 'xx''））;

我相信如果你将它们放在同一条线上，它将是一个单一的交易。如果没有真正的变化，它的好处就是不更新行。如果名字被编入索引，它也很快。

谢谢，
Peter Darley

Mark,
It''s not canonical by any means, but what I do is:

update foo set thing=''stuff'' where name = ''xx'' and thing<>''stuff'';
insert into foo (name, thing) (select ''xx'' as name, ''stuff'' as thing where
not exists (select 1 from foo where name=''xx''));

I believe if you put these on the same line it will be a single
transaction. It has the benefit of not updating the row if there aren''t
real changes. It''s plenty quick too, if name is indexed.

Thanks,
Peter Darley

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

提示7：别忘了增加免费空间地图设置

---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings

这篇关于最常用的“更新或插入”方式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！