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

Re: UPSERT

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Jonathan Scher <js(at)oxado(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: UPSERT
Date: 2007-03-02 14:35:20
Message-ID: 45E83628.9040106@dunslane.net (view raw or flat)
Thread:
Lists: pgsql-hackers
Jonathan Scher wrote:
> 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?

Good. Some thoughts from the top of the head:

Is "insert or on failure update" semantically equivalent to "update or 
on failure insert"? If not the former seems more desirable to me anyway.

What are the syntax alternatives? This one from MySQL doesn't seem too 
bad, but it would be good to have them all on the table.

My instinct would be to follow your first strategy, i.e. detect which 
path is needed rather than try one and then if it fails do the other.

cheers

andrew


In response to

  • UPSERT at 2007-03-02 13:58:46 from Jonathan Scher

Responses

  • Re: UPSERT at 2007-03-02 14:49:00 from Florian G. Pflug
  • Re: UPSERT at 2007-03-02 15:13:18 from Tom Lane

pgsql-hackers by date

Next:From: Florian G. PflugDate: 2007-03-02 14:49:00
Subject: Re: UPSERT
Previous:From: Jonathan ScherDate: 2007-03-02 13:58:46
Subject: UPSERT

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