pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
MySql 'REPLACE'
6+ messages / 5 participants
[nested] [flat]

* MySql 'REPLACE'
@ 2001-04-25 12:38  Alessio Bragadini <alessio@albourne.com>
  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 13:06  Thomas Good <tomg@admin.nrnet.org>
  parent: Alessio Bragadini <alessio@albourne.com>
  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 13:43  Thomas Swan <tswan@ics.olemiss.edu>
  parent: 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 15:25  Alessio Bragadini <alessio@albourne.com>
  parent: Thomas Swan <tswan@ics.olemiss.edu>
  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>
  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-26 15:37  Vivek Khera <khera@kcilink.com>
  parent: Diehl, Jeffrey <jdiehl@sandia.gov>
  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