Thread: INSERT OU UPDATE WITHOUT SELECT?
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.
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.
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/
> 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.
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
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 >
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.
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