pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMySql 'REPLACE'
6+ messages / 5 participants
[nested] [flat]
* MySql 'REPLACE'
@ 2001-04-25 12:38 Alessio Bragadini <alessio@albourne.com>
2001-04-25 13:06 ` Re: MySql 'REPLACE' Thomas Good <tomg@admin.nrnet.org>
2001-04-25 13:43 ` Re: MySql 'REPLACE' Thomas Swan <tswan@ics.olemiss.edu>
0 siblings, 2 replies; 6+ messages in thread
From: Alessio Bragadini @ 2001-04-25 12:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: MySql 'REPLACE'
2001-04-25 12:38 MySql 'REPLACE' Alessio Bragadini <alessio@albourne.com>
@ 2001-04-25 13:06 ` Thomas Good <tomg@admin.nrnet.org>
1 sibling, 0 replies; 6+ messages in thread
From: Thomas Good @ 2001-04-25 13:06 UTC (permalink / raw)
To: Alessio Bragadini <alessio@albourne.com>; +Cc: pgsql-sql
On Wed, 25 Apr 2001, Alessio Bragadini wrote:
> 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.
Hi, I did this in moving a query from a deprecated* (INSERT INTO ... SELECT)
form to two separate queries...using perl. My statement handle for the
select cursor grabbed all of the values and then passed them to an insert.
In your case I'd guess you could grab the pkey in a $sth->fetchrow_array()
(or hashref) and do a delete & insert in a $dbh->do() series?
Good luck,
Tom
* deprecated according to SQL In A Nutshell and other SQL3 ref guides.
--------------------------------------------------------------------
SVCMC - Center for Behavioral Health
--------------------------------------------------------------------
Thomas Good tomg@ { admin | q8 } .nrnet.org
Database Administrator Phone: 718-354-5528
Staten Island Region Mobile: 917-282-7359
--------------------------------------------------------------------
Powered by: PostgreSQL s l a c k w a r e FreeBSD:
RDBMS |---------- linux The Power To Serve
--------------------------------------------------------------------
/* We prefer linguistic vigor over mathematical rigor. */
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: MySql 'REPLACE'
2001-04-25 12:38 MySql 'REPLACE' Alessio Bragadini <alessio@albourne.com>
@ 2001-04-25 13:43 ` Thomas Swan <tswan@ics.olemiss.edu>
2001-04-25 15:25 ` Re: MySql 'REPLACE' Alessio Bragadini <alessio@albourne.com>
1 sibling, 1 reply; 6+ messages in thread
From: Thomas Swan @ 2001-04-25 13:43 UTC (permalink / raw)
To: Alessio Bragadini <alessio@albourne.com>; +Cc: pgsql-sql
At 4/25/2001 07:38 AM, you wrote:
>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.
You should be able to do this with two separate queries inside a transaction.
The only part I'm not clear on is whether to use an 'and' or an 'or' on the
delete. Check and see if all values have to match or if partial matches,
i.e. only one of the columns, are acceptable. If it does partial matching,
then use the 'or', otherwise use the 'and'.
Give this a try...
begin;
delete from table where (column=value) and (column=value) and ...;
insert into table (column, column...) values (value, value...);
end;
From what you've given me, I think this would emulate that behavior.
Hope it helps...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: MySql 'REPLACE'
2001-04-25 12:38 MySql 'REPLACE' Alessio Bragadini <alessio@albourne.com>
2001-04-25 13:43 ` Re: MySql 'REPLACE' Thomas Swan <tswan@ics.olemiss.edu>
@ 2001-04-25 15:25 ` Alessio Bragadini <alessio@albourne.com>
0 siblings, 0 replies; 6+ messages in thread
From: Alessio Bragadini @ 2001-04-25 15:25 UTC (permalink / raw)
To: Thomas Swan <tswan@ics.olemiss.edu>; +Cc: pgsql-sql
Thomas Swan wrote:
> You should be able to do this with two separate queries inside a
> transaction.
Yes, sorry, I was not clear enough. Unfortunately, the function I need
to write is a "generic" one that takes a number of fields/values and
generate a SQL instruction. So, there is no previous information about
the underlining table structure. What I did is a query to pg_class and
other pg_* tables to get a list of unique indexes and build the DELETEs
needed prior the INSERT.
> The only part I'm not clear on is whether to use an 'and' or an 'or'
> on the delete. Check and see if all values have to match or if
> partial matches, i.e. only one of the columns, are acceptable. If it
> does partial matching, then use the 'or', otherwise use the 'and'.
I went for AND.
You're welcome to check if my (Perl) code is completely wrong :-)
This is the MySql version:
sub sqlReplace {
my($self, $table, $data) = @_;
my($names, $values);
foreach (keys %$data) {
if (/^-/) {
$values .= "\n $data->{$_},";
s/^-//;
} else {
$values .= "\n " . $self->{_dbh}->quote($data->{$_}) . ',';
}
$names .= "$_,";
}
chop($names);
chop($values);
my $sql = "REPLACE INTO $table ($names) VALUES($values)\n";
$self->sqlConnect();
return $self->sqlDo($sql) or errorLog($sql);
}
and my PostgreSQL version:
sub sqlReplace {
my($self, $table, $data) = @_;
my($names, $values);
foreach (keys %$data) {
if (/^-/) {
$values .= "\n $data->{$_},";
s/^-//;
} else {
$values .= "\n " . $self->{_dbh}->quote($data->{$_}) . ',';
}
$names .= "$_,";
}
chop($names);
chop($values);
# We study the table structure - this code comes from psql -E
my $cols = $self->{_dbh}->selectcol_arrayref
(q{SELECT a.attname
FROM pg_class c, pg_attribute a
WHERE c.relname = ? AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum}, undef, $table) || [];
unshift @$cols, ''; # To have values starting at index 1
my $all_uniq = $self->{_dbh}->selectcol_arrayref
(q{SELECT indkey
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = ? AND c.oid = i.indrelid AND
i.indexrelid = c2.oid AND indisunique IS TRUE},
undef, $table) || [];
$self->{_dbh}->{AutoCommit} = 0; # BEGIN TRANSACTION
foreach (@$all_uniq) {
my @acols = @$cols[split];
my $check = 1;
map {$check &&= defined $data->{$_}} @acols;
next unless $check;
my $sql = "DELETE FROM $table WHERE " .
join (' AND ',
map "$_ = " . $self->{_dbh}->quote($data->{$_}), @acols);
$self->{_dbh}->do ($sql);
}
my $sql = "INSERT INTO $table ($names) VALUES ($values)";
$self->{_dbh}->do($sql);
$self->{_dbh}->commit; # END TRANSACTION
# return $self->sqlDo($sql) or errorLog($sql);
}
--
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: MySql 'REPLACE'
@ 2001-04-25 20:18 Diehl, Jeffrey <jdiehl@sandia.gov>
2001-04-26 15:37 ` Re: MySql 'REPLACE' Vivek Khera <khera@kcilink.com>
0 siblings, 1 reply; 6+ messages in thread
From: Diehl, Jeffrey @ 2001-04-25 20:18 UTC (permalink / raw)
To: 'Alessio Bragadini' <alessio@albourne.com>; pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: MySql 'REPLACE'
2001-04-25 20:18 RE: MySql 'REPLACE' Diehl, Jeffrey <jdiehl@sandia.gov>
@ 2001-04-26 15:37 ` Vivek Khera <khera@kcilink.com>
0 siblings, 0 replies; 6+ messages in thread
From: Vivek Khera @ 2001-04-26 15:37 UTC (permalink / raw)
To: pgsql-sql
>>>>> "JD" == Jeffrey Diehl <jdiehl@sandia.gov> writes:
JD> Replace into does an insert by default. If however, there is
JD> already a record which would cause a unique index collision, then
JD> mysql does an update on that record. I want to prevent my
JD> application from having to do all of that bookkeeping.
No, MySQL does not do an update in that case. It deletes the existing
row, then does the insert. This really screws you over when you have
auto incrementing (sequence) columns, and screws you when you are not
specifying every field in the row (those columns take their defaults,
not the original values of the old row). It is really a useless
function, as far as I can tell.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-04-26 15:37 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-25 12:38 MySql 'REPLACE' Alessio Bragadini <alessio@albourne.com>
2001-04-25 13:06 ` Thomas Good <tomg@admin.nrnet.org>
2001-04-25 13:43 ` Thomas Swan <tswan@ics.olemiss.edu>
2001-04-25 15:25 ` Alessio Bragadini <alessio@albourne.com>
2001-04-25 20:18 RE: MySql 'REPLACE' Diehl, Jeffrey <jdiehl@sandia.gov>
2001-04-26 15:37 ` Vivek Khera <khera@kcilink.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox