Skip site navigation (1) Skip section navigation (2)

UPSERT

From: Jonathan Scher <js(at)oxado(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: UPSERT
Date: 2007-03-02 13:58:46
Message-ID: 45E82D96.1060406@oxado.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hello,

I'd like to work on TODO item:
 > Add REPLACE or UPSERT command that does UPDATE, or on failure, INSERT

could you please tell me if I'm going in the right way?

There are some different syntaxes possible, but MySQL has an interesting 
one here:
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
This allow to make an insert, and if the key is already there to modify 
the value depending on the current one.

Then I have two choices possible:
- Search for existing tuples among key or unique constraint, then if 
nothing is found, insert it.
- Try to insert a new row, catch if there is any error, and then search 
for all tuple matching.

As it would be a new command, I have no idea on what the data could be.
Does syntax meet your needs? Which choice should I implement?

Regards
Jonathan Scher

Responses

  • Re: UPSERT at 2007-03-02 14:35:20 from Andrew Dunstan

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2007-03-02 14:35:20
Subject: Re: UPSERT
Previous:From: Jonah H. HarrisDate: 2007-03-02 13:45:17
Subject: Re: xlogViewer / xlogdump

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group