Re: [GENERAL] most idiomatic way to update or insert?

I use stored procedures : create function insertorupdate() UPDATE mytable WHERE ... SET ... IF NOT FOUND THEN INSERT INTO mytable ... END IF; You lose flecibility in your request though. I wish Postgresql had an INSERT OR UPDATE like MySQL does. So

Re: [GENERAL] most idiomatic way to update or insert?

Mark Harrison wrote: I've been doing something like delete from foo where name = 'xx'; insert into foo values('xx',1,2,...); but I've been wondering if there's a more idiomatic or canonical way to do this. The delete+insert isn't quite the same as an update since you might have foreign keys

Re: [GENERAL] most idiomatic way to update or insert?

Hi, I prefer to update and if the number of updated rows equals 0 do an insert. So in case of update I need only one roundtrip. If insert is far more common in this case it might be better try insert and catch the error. But I try to avoid running on an error intentionally. First delete and

Re: [GENERAL] most idiomatic way to update or insert?

An update or insert would be useful sometimes, but it's not always necessary. Indeed, if I find I don't know whether I'm adding or updating something I take a long hard look at my design - it ususally means I've not thought clearly about something. ... Can you give an actual example of where

Re: [GENERAL] most idiomatic way to update or insert?

Richard Huxton wrote: An update or insert would be useful sometimes, but it's not always necessary. Indeed, if I find I don't know whether I'm adding or updating something I take a long hard look at my design - it ususally means I've not thought clearly about something. Can you give an actual

Re: [GENERAL] most idiomatic way to update or insert?

Ron St-Pierre wrote: Richard Huxton wrote: An update or insert would be useful sometimes, but it's not always necessary. Indeed, if I find I don't know whether I'm adding or updating something I take a long hard look at my design - it ususally means I've not thought clearly about something.

Re: [GENERAL] most idiomatic way to update or insert?

Richard Huxton wrote: Ron St-Pierre wrote: Richard Huxton wrote: An update or insert would be useful sometimes, but it's not always necessary. Indeed, if I find I don't know whether I'm adding or updating something I take a long hard look at my design - it ususally means I've not thought

Re: [GENERAL] most idiomatic way to update or insert?

Ron St-Pierre [EMAIL PROTECTED] writes: BTW these updates do take longer than we'd like so I would appreciate more input on how this setup could be redesigned. Where is the input coming from? One option is to batch changes. If you just insert into a log table whenever new data is available,

Re: [GENERAL] most idiomatic way to update or insert?

Ron St-Pierre wrote: Okay, this simple example really exists, but the simple table also includes a date that the stock was last traded, so we have: stock symbol, stock exchange, high, low, open, close, volume, date, plus a few more fields [snip more details] BTW these updates do take longer than

Re: [GENERAL] most idiomatic way to update or insert?

Hi, I prefer to update and if the number of updated rows equals 0 do an insert. So in case of update I need only one roundtrip. If insert is far more common in this case it might be better try insert and catch the error. But I try to avoid running on an error intentionally. When logging to a

Re: [GENERAL] most idiomatic way to update or insert?

Greg Stark wrote: Ron St-Pierre [EMAIL PROTECTED] writes: BTW these updates do take longer than we'd like so I would appreciate more input on how this setup could be redesigned. Where is the input coming from? One option is to batch changes. snip Something like update current_stock_price