From an earlier posting....
Replace into does an insert by default. If however, there is already a
record which would cause a unique index collision, then mysql does an update
on that record. I want to prevent my application from having to do all of
that bookkeeping.
Peter Eisentraut has suggested:
begin transaction;
update ...
<if zero rows> insert ...
<endif>
commit;
This seems like a good way to go. He suggested that I write a PL/pgSQL
function to do the work. I really don't have time to climb the learning
curve on this so I might just write a perl module to do it, and a few other
things, for me.
Thanx for your help.
If there is AN EVEN EASIER way, please let me know.
Mike Diehl,
Network Monitoring Tool Devl.
284-3137
jdiehl@sandia.gov
> -----Original Message-----
> From: Alessio Bragadini [mailto:alessio@albourne.com]
> Sent: April 25, 2001 6:39 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] MySql 'REPLACE'
>
>
> I am working in porting the Slash software from MySql to PostgreSQL. I
> stepped in MySql's REPLACE command (a SQL command) that to my
> knowledge
> is not supported by PostgreSQL and it's not standard SQL. According to
> MySql's manual:
>
> "REPLACE works exactly like INSERT, except that if an old
> record in the
> table has the same value as a new record on a unique index, the old
> record is
> deleted before the new record is inserted. See section 7.21 INSERT
> syntax."
>
> REPLACE INTO table (column, column...) VALUES (value, value...)
>
> Has anyone had any experience about how to simulate it under
> PostgreSQL?
> I am using Perl and I can move most of the thing into application
> anyway.
>
> Thanks.
>
> --
> Alessio F. Bragadini alessio@albourne.com
> APL Financial Services http://village.albourne.com
> Nicosia, Cyprus phone: +357-2-755750
>
> "It is more complicated than you think"
> -- The Eighth Networking Truth from RFC 1925
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
