Thread: INSERT OU UPDATE WITHOUT SELECT?

INSERT OU UPDATE WITHOUT SELECT?

From
Waldomiro
Date:
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.


Re: INSERT OU UPDATE WITHOUT SELECT?

From
"Jonah H. Harris"
Date:
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/

Re: INSERT OU UPDATE WITHOUT SELECT?

From
PFC
Date:
> 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.


Re: INSERT OU UPDATE WITHOUT SELECT?

From
Alvaro Herrera
Date:
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

Re: INSERT OU UPDATE WITHOUT SELECT?

From
"Dave Dutcher"
Date:
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
>


Re: INSERT OU UPDATE WITHOUT SELECT?

From
"D'Arcy J.M. Cain"
Date:
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.

Re: INSERT OU UPDATE WITHOUT SELECT?

From
"Jim C. Nasby"
Date:
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