Thread: Replace into...

Coming from MySQL, I'm kind of sad to learn that
"REPLACE INTO" isn't part of Postgres.  However,
I understand totally why it is not.

The problem, though, is that I need this type of
functionality... To INSERT or UPDATE depending on
whether or not the record is already there.  I've
spent the last several hours trying to figure out
how I can set up a trigger to do this.  Searching
on Google and Yahoo turns up obscure references
to this approach but can't find anything a bit more
informative.

I've tried going to the postgresql.org website to
look at the docs (again; I searched last night but
couldn't come up with anything I could see with
my noobie eyes that could help me) but the site
appears to be down.

Could someone give me an example, or point me in
the right direction to a trigger that would act
like a "REPLACE INTO"?

If possible, could you also CC me at

jcboget
at
yahoo
dot
com

as this is my work address (that I'm accessing
from home) and the interface to read the mail is
very poor.

I appreciate any assistance you can provide!!

thnx,
Christoph

Hi Chris.......

> The problem, though, is that I need this type of functionality... To
> INSERT or UPDATE depending on whether or not the record is already
> there.  I've spent the last several hours trying to figure out how I can
> set up a trigger to do this.

Don't know about a trigger, but the way I do this is do a SELECT, if no
rows are returned I do an INSERT, if rows are returned I do an UPDATE.

Somebody else probably has a more clever way to do it though......

brew

 ==========================================================================
                  Strange Brew   (brew@theMode.com)
     Check out my Musician's Online Database Exchange (The MODE Pages)
                        http://www.TheMode.com
 ==========================================================================

Am Son, 2003-07-06 um 02.22 schrieb brew@theMode.com:
> Hi Chris.......
>
> > The problem, though, is that I need this type of functionality... To
> > INSERT or UPDATE depending on whether or not the record is already
> > there.  I've spent the last several hours trying to figure out how I can
> > set up a trigger to do this.
>
> Don't know about a trigger, but the way I do this is do a SELECT, if no
> rows are returned I do an INSERT, if rows are returned I do an UPDATE.
>
> Somebody else probably has a more clever way to do it though......

I would do this unconditionally.
UPDATE ... FROM ... WHERE EXISTS (SELECT ...)
INSERT INTO ... WHERE NOT EXISTS (SELECT ...)

That are still 2 queries, but better than playing around with triggers.

HTH
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

>> Somebody else probably has a more clever way to do it though......
>I would do this unconditionally.
>UPDATE ... FROM ... WHERE EXISTS (SELECT ...)
>INSERT INTO ... WHERE NOT EXISTS (SELECT ...)
>That are still 2 queries, but better than playing around with triggers.

I need to do this in a trigger, though.  The trigger I need to create
is one that will run when I try to update a view.  I'm going to need to
update a different table, instead.

thnx,
Chris

Boget, Chris wrote:

> >> Somebody else probably has a more clever way to do it though......
> >I would do this unconditionally.
> >UPDATE ... FROM ... WHERE EXISTS (SELECT ...)
> >INSERT INTO ... WHERE NOT EXISTS (SELECT ...)
> >That are still 2 queries, but better than playing around with triggers.
>
Hmm, in Oracle, before we had MERGE INTO
we did it in the Procedureal language:
- You try to update
- if it doesnt work, insert.

this would lead to such a structure:

update;
if not found then
    insert;
end if;

I hope this gives you a hint.
Cheers, Dani