pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedINSERT OU UPDATE WITHOUT SELECT?
7+ messages / 7 participants
[nested] [flat]
* INSERT OU UPDATE WITHOUT SELECT?
@ 2006-05-30 18:29 Waldomiro <waldomiro@shx.com.br>
2006-05-30 21:05 ` Re: INSERT OU UPDATE WITHOUT SELECT? Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Waldomiro @ 2006-05-30 18:29 UTC (permalink / raw)
To: pgsql-performance
Hi,
Is there a command to Insert a record If It does not exists and a update
if It exists?
I do not want to do a select before a insert or update.
I mean the postgres should test if a record exist before insert and if
It exist then the postgres must do an update instead an insert.
Thanks,
WMiro.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT OU UPDATE WITHOUT SELECT?
2006-05-30 18:29 INSERT OU UPDATE WITHOUT SELECT? Waldomiro <waldomiro@shx.com.br>
@ 2006-05-30 21:05 ` Jonah H. Harris <jonah.harris@gmail.com>
2006-05-30 22:35 ` Re: INSERT OU UPDATE WITHOUT SELECT? PFC <lists@peufeu.com>
0 siblings, 1 reply; 7+ messages in thread
From: Jonah H. Harris @ 2006-05-30 21:05 UTC (permalink / raw)
To: Waldomiro <waldomiro@shx.com.br>; +Cc: pgsql-performance
On 5/30/06, Waldomiro <waldomiro@shx.com.br> wrote:
> Is there a command to Insert a record If It does not exists and a update if
> It exists?
Sure, it's called MERGE. See http://en.wikipedia.org/wiki/Merge_%28SQL%29
> I mean the postgres should test if a record exist before insert and if It
> exist then the postgres must do an update instead an insert.
PostgreSQL does not support MERGE at the moment, sorry.
--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT OU UPDATE WITHOUT SELECT?
2006-05-30 18:29 INSERT OU UPDATE WITHOUT SELECT? Waldomiro <waldomiro@shx.com.br>
2006-05-30 21:05 ` Re: INSERT OU UPDATE WITHOUT SELECT? Jonah H. Harris <jonah.harris@gmail.com>
@ 2006-05-30 22:35 ` PFC <lists@peufeu.com>
2006-05-30 22:38 ` Re: INSERT OU UPDATE WITHOUT SELECT? Alvaro Herrera <alvherre@commandprompt.com>
2006-05-30 22:54 ` Re: INSERT OU UPDATE WITHOUT SELECT? Dave Dutcher <dave@tridecap.com>
0 siblings, 2 replies; 7+ messages in thread
From: PFC @ 2006-05-30 22:35 UTC (permalink / raw)
To: Jonah H. Harris <jonah.harris@gmail.com>; Waldomiro <waldomiro@shx.com.br>; +Cc: pgsql-performance
> PostgreSQL does not support MERGE at the moment, sorry.
Issue an UPDATE, and watch the rowcount ; if the rowcount is 0, issue an
INSERT.
Be prepared to retry if another transaction has inserted the row
meanwhile, though.
MERGE would be really useful.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT OU UPDATE WITHOUT SELECT?
2006-05-30 18:29 INSERT OU UPDATE WITHOUT SELECT? Waldomiro <waldomiro@shx.com.br>
2006-05-30 21:05 ` Re: INSERT OU UPDATE WITHOUT SELECT? Jonah H. Harris <jonah.harris@gmail.com>
2006-05-30 22:35 ` Re: INSERT OU UPDATE WITHOUT SELECT? PFC <lists@peufeu.com>
@ 2006-05-30 22:38 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 7+ messages in thread
From: Alvaro Herrera @ 2006-05-30 22:38 UTC (permalink / raw)
To: PFC <lists@peufeu.com>; +Cc: Jonah H. Harris <jonah.harris@gmail.com>; Waldomiro <waldomiro@shx.com.br>; pgsql-performance
PFC wrote:
> >PostgreSQL does not support MERGE at the moment, sorry.
>
> Issue an UPDATE, and watch the rowcount ; if the rowcount is 0,
> issue an INSERT.
> Be prepared to retry if another transaction has inserted the row
> meanwhile, though.
Oh, you mean, like the example that's in the documentation?
http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
Example 36-1
> MERGE would be really useful.
It has been discussed before -- MERGE is something different.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT OU UPDATE WITHOUT SELECT?
2006-05-30 18:29 INSERT OU UPDATE WITHOUT SELECT? Waldomiro <waldomiro@shx.com.br>
2006-05-30 21:05 ` Re: INSERT OU UPDATE WITHOUT SELECT? Jonah H. Harris <jonah.harris@gmail.com>
2006-05-30 22:35 ` Re: INSERT OU UPDATE WITHOUT SELECT? PFC <lists@peufeu.com>
@ 2006-05-30 22:54 ` Dave Dutcher <dave@tridecap.com>
2006-05-30 23:05 ` Re: INSERT OU UPDATE WITHOUT SELECT? D'Arcy J.M. Cain <darcy@druid.net>
1 sibling, 1 reply; 7+ messages in thread
From: Dave Dutcher @ 2006-05-30 22:54 UTC (permalink / raw)
To: 'PFC' <lists@peufeu.com>; +Cc: pgsql-performance
What I do when I'm feeling lazy is execute a delete statement and then
an insert. I only do it when I'm inserting/updating a very small number
of rows, so I've never worried if its optimal for performance. Besides
I've heard that an update in postgres is similar in performance to a
delete/insert.
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of PFC
> Sent: Tuesday, May 30, 2006 5:35 PM
> To: Jonah H. Harris; Waldomiro
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] INSERT OU UPDATE WITHOUT SELECT?
>
>
> > PostgreSQL does not support MERGE at the moment, sorry.
>
> Issue an UPDATE, and watch the rowcount ; if the
> rowcount is 0, issue an
> INSERT.
> Be prepared to retry if another transaction has
> inserted the row
> meanwhile, though.
>
> MERGE would be really useful.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT OU UPDATE WITHOUT SELECT?
2006-05-30 18:29 INSERT OU UPDATE WITHOUT SELECT? Waldomiro <waldomiro@shx.com.br>
2006-05-30 21:05 ` Re: INSERT OU UPDATE WITHOUT SELECT? Jonah H. Harris <jonah.harris@gmail.com>
2006-05-30 22:35 ` Re: INSERT OU UPDATE WITHOUT SELECT? PFC <lists@peufeu.com>
2006-05-30 22:54 ` Re: INSERT OU UPDATE WITHOUT SELECT? Dave Dutcher <dave@tridecap.com>
@ 2006-05-30 23:05 ` D'Arcy J.M. Cain <darcy@druid.net>
2006-05-31 06:29 ` Re: INSERT OU UPDATE WITHOUT SELECT? Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 7+ messages in thread
From: D'Arcy J.M. Cain @ 2006-05-30 23:05 UTC (permalink / raw)
To: Dave Dutcher <dave@tridecap.com>; +Cc: lists@peufeu.com; pgsql-performance
On Tue, 30 May 2006 17:54:00 -0500
"Dave Dutcher" <dave@tridecap.com> wrote:
> What I do when I'm feeling lazy is execute a delete statement and then
> an insert. I only do it when I'm inserting/updating a very small number
> of rows, so I've never worried if its optimal for performance. Besides
> I've heard that an update in postgres is similar in performance to a
> delete/insert.
Well, they are basically the same operation in PostgreSQL. An update
adds a row to the end and marks the old one dead. A delete/insert
marks the row dead and adds one at the end. There may be some
optimization if the engine does both in one operation.
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT OU UPDATE WITHOUT SELECT?
2006-05-30 18:29 INSERT OU UPDATE WITHOUT SELECT? Waldomiro <waldomiro@shx.com.br>
2006-05-30 21:05 ` Re: INSERT OU UPDATE WITHOUT SELECT? Jonah H. Harris <jonah.harris@gmail.com>
2006-05-30 22:35 ` Re: INSERT OU UPDATE WITHOUT SELECT? PFC <lists@peufeu.com>
2006-05-30 22:54 ` Re: INSERT OU UPDATE WITHOUT SELECT? Dave Dutcher <dave@tridecap.com>
2006-05-30 23:05 ` Re: INSERT OU UPDATE WITHOUT SELECT? D'Arcy J.M. Cain <darcy@druid.net>
@ 2006-05-31 06:29 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 7+ messages in thread
From: Jim C. Nasby @ 2006-05-31 06:29 UTC (permalink / raw)
To: D'Arcy J.M. Cain <darcy@druid.net>; +Cc: Dave Dutcher <dave@tridecap.com>; lists@peufeu.com; pgsql-performance
On Tue, May 30, 2006 at 07:05:08PM -0400, D'Arcy J.M. Cain wrote:
> On Tue, 30 May 2006 17:54:00 -0500
> "Dave Dutcher" <dave@tridecap.com> wrote:
> > What I do when I'm feeling lazy is execute a delete statement and then
> > an insert. I only do it when I'm inserting/updating a very small number
> > of rows, so I've never worried if its optimal for performance. Besides
> > I've heard that an update in postgres is similar in performance to a
> > delete/insert.
>
> Well, they are basically the same operation in PostgreSQL. An update
> adds a row to the end and marks the old one dead. A delete/insert
> marks the row dead and adds one at the end. There may be some
> optimization if the engine does both in one operation.
The new tuple will actually go on the same page during an update, if
possible. If not, the FSM is consulted. Appending to the end of the
table is a last resort.
Update is more effecient than delete/insert. First, it's one less
statement to parse and plan. Second, AFAIK insert always goes to the
FSM; it has no way to know you're replacing the row(s) you just deleted.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-05-31 06:29 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-30 18:29 INSERT OU UPDATE WITHOUT SELECT? Waldomiro <waldomiro@shx.com.br>
2006-05-30 21:05 ` Jonah H. Harris <jonah.harris@gmail.com>
2006-05-30 22:35 ` PFC <lists@peufeu.com>
2006-05-30 22:38 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-05-30 22:54 ` Dave Dutcher <dave@tridecap.com>
2006-05-30 23:05 ` D'Arcy J.M. Cain <darcy@druid.net>
2006-05-31 06:29 ` Jim C. Nasby <jnasby@pervasive.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox