Thread: INSERT ON DUPLICATE KEY UPDATE
How may I do a INSERT ON DUPLICATE KEY UPDATE like in mysql: http://dev.mysql.com/doc/mysql/en/INSERT.html ? Eduardo
I would suspect you would need to write a trigger to do this.
-----Original Message-----
From: Eduardo Pérez Ureta [mailto:eperez@it.uc3m.es]
Sent: Friday, June 18, 2004 9:38 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] INSERT ON DUPLICATE KEY UPDATE
How may I do a INSERT ON DUPLICATE KEY UPDATE like in mysql:
http://dev.mysql.com/doc/mysql/en/INSERT.html
?
Eduardo
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On 2004-06-18 17:19:40 UTC, Duane Lee - EGOVX wrote: > I would suspect you would need to write a trigger to do this. It seems the mysql way of doing this is easier and safer. Why is that not implemented in postgresql? Is it better done with a trigger or with any other way? Eduardo > -----Original Message----- > From: Eduardo Pérez Ureta [mailto:eperez@it.uc3m.es] > Sent: Friday, June 18, 2004 9:38 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] INSERT ON DUPLICATE KEY UPDATE > > > How may I do a INSERT ON DUPLICATE KEY UPDATE like in mysql: > http://dev.mysql.com/doc/mysql/en/INSERT.html > ?
Eduardo Pérez Ureta wrote: > On 2004-06-18 17:19:40 UTC, Duane Lee - EGOVX wrote: > >>I would suspect you would need to write a trigger to do this. > > > It seems the mysql way of doing this is easier and safer. And non-standard AFAIK. > Why is that not implemented in postgresql? > Is it better done with a trigger or with any other way? Out of curiosity, why don't you know whether you're inserting or updating? It always worries me when I don't know what my application is doing. -- Richard Huxton Archonet Ltd
I agree. You could always do a SELECT and if the record was found then UPDATE otherwise INSERT. A little more effort than MYSQL but again I don't believe the way MYSQL is allowing you to do it is standard.
Duane
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Friday, June 18, 2004 11:02 AM
To: Eduardo Pérez Ureta
Cc: Duane Lee - EGOVX; pgsql-general@postgresql.org
Subject: Re: [GENERAL] INSERT ON DUPLICATE KEY UPDATE
Eduardo Pérez Ureta wrote:
> On 2004-06-18 17:19:40 UTC, Duane Lee - EGOVX wrote:
>
>>I would suspect you would need to write a trigger to do this.
>
>
> It seems the mysql way of doing this is easier and safer.
And non-standard AFAIK.
> Why is that not implemented in postgresql?
> Is it better done with a trigger or with any other way?
Out of curiosity, why don't you know whether you're inserting or
updating? It always worries me when I don't know what my application is
doing.
--
Richard Huxton
Archonet Ltd
IIRC the standard syntax is based on db2's horrendous merge on command,
which was only added to the standard a couple months back.
IIRC the main downside to the select/update method is it introduces a
race condition that can only be solved by locking the table; not an
issue for most my$ql apps but would be frowned upon by most postgresql
users.
Robert Treat
On Fri, 2004-06-18 at 15:00, Duane Lee - EGOVX wrote:
> I agree. You could always do a SELECT and if the record was found then
> UPDATE otherwise INSERT. A little more effort than MYSQL but again I
> don't believe the way MYSQL is allowing you to do it is standard.
>
> Duane
>
> -----Original Message-----
> From: Richard Huxton [ mailto:dev@archonet.com <mailto:dev@archonet.com>
> ]
> Sent: Friday, June 18, 2004 11:02 AM
> To: Eduardo Pérez Ureta
> Cc: Duane Lee - EGOVX; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] INSERT ON DUPLICATE KEY UPDATE
>
>
> Eduardo Pérez Ureta wrote:
> > On 2004-06-18 17:19:40 UTC, Duane Lee - EGOVX wrote:
> >
> >>I would suspect you would need to write a trigger to do this.
> >
> >
> > It seems the mysql way of doing this is easier and safer.
>
> And non-standard AFAIK.
>
> > Why is that not implemented in postgresql?
> > Is it better done with a trigger or with any other way?
>
> Out of curiosity, why don't you know whether you're inserting or
> updating? It always worries me when I don't know what my application is
> doing.
>
> --
> Richard Huxton
> Archonet Ltd
>
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On 2004-06-18 18:02:18 UTC, Richard Huxton wrote: > Out of curiosity, why don't you know whether you're inserting or > updating? It always worries me when I don't know what my application is > doing. This is the example: I'm downloading a list of products from the web and they already have a product_id (that's the primary key), in my table I have many fields that I use myself like comments, stock, ... But when downloading the list I only have the product_id, the product name, the price and the remote stock. So I must insert data like the remote stock without removing any of my data and leaving all the other columns unmodified.
Robert Treat <xzilla@users.sourceforge.net> wrote: > [snip] > > IIRC the main downside to the select/update method is it introduces a > race condition that can only be solved by locking the table; not an > issue for most my$ql apps but would be frowned upon by most postgresql > users. [snip] One way around the race condition is to insert and, if the insert fails, do an update. If that fails, then something is actually wrong. One caveat regarding the above solution: Even when an insert fails, if there's a column with a sequence in it, that sequence will be incremented nonetheless. Jim
On 2004-06-18 20:15:11 UTC, Robert Treat wrote: > IIRC the standard syntax is based on db2's horrendous merge on command, > which was only added to the standard a couple months back. Great! I didn't know that was standard. I see that Oracle has it: http://www.dba-oracle.com/oracle_tips_rittman_merge.htm And db2 as you said: http://www.databasejournal.com/features/db2/article.php/3322041 Could you explain why db2's merge command is horrendous? Is it better the mysql command? So will postgresql accept this command? > IIRC the main downside to the select/update method is it introduces a > race condition that can only be solved by locking the table; not an > issue for most my$ql apps but would be frowned upon by most postgresql > users. Or course, if you don't need to do locking is a plus. Eduardo
Compare the syntaxes of the two commands. IMHO the my$ql syntax is friendlier and I would have probably preferred that it had become the standard. (I'll leave open the possibility that my$ql's syntax would fall apart in complex scenarios that only db2/oracle/pg are capable of) As to your second question, no, postgresql will not accept this command, and no one has current plans to implement it, though it is on the TODO list so don't be afraid to submit a patch for it yourself (or hire someone to write the patch for you), chances are good it would be accepted. If you can bang it out in the next 2 weeks you could even get it into 7.5 :-) On your third note, I believe that my$ql does lock the table in order to accomplish this, not sure about db2/oracle. Robert Treat On Fri, 2004-06-18 at 16:46, Eduardo Pérez Ureta wrote: > On 2004-06-18 20:15:11 UTC, Robert Treat wrote: > > IIRC the standard syntax is based on db2's horrendous merge on command, > > which was only added to the standard a couple months back. > > Great! I didn't know that was standard. > I see that Oracle has it: > http://www.dba-oracle.com/oracle_tips_rittman_merge.htm > And db2 as you said: > http://www.databasejournal.com/features/db2/article.php/3322041 > > Could you explain why db2's merge command is horrendous? > Is it better the mysql command? > > So will postgresql accept this command? > > > IIRC the main downside to the select/update method is it introduces a > > race condition that can only be solved by locking the table; not an > > issue for most my$ql apps but would be frowned upon by most postgresql > > users. > > Or course, if you don't need to do locking is a plus. > > Eduardo > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On Fri, 2004-06-18 at 16:41, Jim Seymour wrote:
> Robert Treat <xzilla@users.sourceforge.net> wrote:
> >
> [snip]
> >
> > IIRC the main downside to the select/update method is it introduces a
> > race condition that can only be solved by locking the table; not an
> > issue for most my$ql apps but would be frowned upon by most postgresql
> > users.
> [snip]
>
> One way around the race condition is to insert and, if the insert
> fails, do an update. If that fails, then something is actually wrong.
>
> One caveat regarding the above solution: Even when an insert fails, if
> there's a column with a sequence in it, that sequence will be
> incremented nonetheless.
>
The other being that you can't do that within a transaction in
postgresql. Course that should change if alvaro gets nested transactions
done for 7.5. (go alvaro go!)
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Something like INSERT OR UPDATE TABLE foo SET bar=something WHERE condition in meaning of "try to insert, if some unique constraint for some column (or index) fail, make just an update" would be nice.... ----- Original Message ----- From: "Eduardo Pérez Ureta" <eperez@it.uc3m.es> To: <pgsql-general@postgresql.org> Sent: Friday, June 18, 2004 6:38 PM Subject: [GENERAL] INSERT ON DUPLICATE KEY UPDATE > How may I do a INSERT ON DUPLICATE KEY UPDATE like in mysql: > http://dev.mysql.com/doc/mysql/en/INSERT.html > ? > > Eduardo > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >
On Fri, Jun 18, 2004 at 08:36:21PM +0000, Eduardo Pérez Ureta wrote: > > On 2004-06-18 18:02:18 UTC, Richard Huxton wrote: > > Out of curiosity, why don't you know whether you're inserting or > > updating? It always worries me when I don't know what my application is > > doing. > > This is the example: > > I'm downloading a list of products from the web and they already have a > product_id (that's the primary key), in my table I have many fields that > I use myself like comments, stock, ... But when downloading the list I > only have the product_id, the product name, the price and the remote > stock. So I must insert data like the remote stock without removing any > of my data and leaving all the other columns unmodified. This smells of that you shouldn't use the product_id as your primary key (I know, this won't solve your problem as the insert will still fail since most likely you'll want product_id to be unique regardless of primary-key-ness). Just a thought. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346