Thread: MySql 'REPLACE'

MySql 'REPLACE'

From
Alessio Bragadini
Date:
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


Re: MySql 'REPLACE'

From
Thomas Good
Date:
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
PowerTo Serve
 
--------------------------------------------------------------------
    /* We prefer linguistic vigor over mathematical rigor. */ 



Re: MySql 'REPLACE'

From
Alessio Bragadini
Date:
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 1my $all_uniq = $self->{_dbh}->selectcol_arrayref  (q{SELECT indkey
FROMpg_class c, pg_class c2, pg_index i      WHERE c.relname = ? AND c.oid = i.indrelid AND        i.indexrelid =
c2.oidAND indisunique IS TRUE},    undef, $table) || [];$self->{_dbh}->{AutoCommit} = 0; # BEGIN TRANSACTIONforeach
(@$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


use of arrow keys to traverse history

From
"Peter J. Schoenster"
Date:
Hi,

Not sure where this question belongs ... I thought postgresql was 
running under the bash shell where I can use up and down arrow 
keys to traverse my command history. I can do this in mysql but 
oddly not in oracle or postgresql.  

/home/postgres

-rw-r--r--   1 postgres postgres     1422 Feb 16 15:50 .Xdefaults
-rw-------   1 postgres postgres      458 Feb 17 16:59 .bash_history
-rw-r--r--   1 postgres postgres       24 Feb 16 15:50 .bash_logout
-rw-r--r--   1 postgres postgres      230 Feb 16 15:50 .bash_profile
-rw-r--r--   1 postgres postgres      313 Feb 17 16:36 .bashrc

Which in my ignorance leads me to believe that postgres will run in 
the bash shell and so I expect the use of arrow keys or command 
history.

Clues appreciated.

Peter

---------------------------
"Reality is that which, when you stop believing in it, doesn't go
away".               -- Philip K. Dick


Re: use of arrow keys to traverse history

From
Andrew Perrin
Date:
These do suggest (although not require) that the *user* postgres will be
running bash when logged in. To check for sure, do:

finger postgres

which will give you the current shell among other things.

However, this doesn't speak to what I think you're asking, which is
command history and completion within psql (the program), not postgres
(the user).  If that's what you want, you have to compile psql with gnu
readline support, which means you also have to have the gnu readline
libraries available on your machine.

Andy Perrin

"Peter J. Schoenster" wrote:
> 
> Hi,
> 
> Not sure where this question belongs ... I thought postgresql was
> running under the bash shell where I can use up and down arrow
> keys to traverse my command history. I can do this in mysql but
> oddly not in oracle or postgresql.
> 
> /home/postgres
> 
> -rw-r--r--   1 postgres postgres     1422 Feb 16 15:50 .Xdefaults
> -rw-------   1 postgres postgres      458 Feb 17 16:59 .bash_history
> -rw-r--r--   1 postgres postgres       24 Feb 16 15:50 .bash_logout
> -rw-r--r--   1 postgres postgres      230 Feb 16 15:50 .bash_profile
> -rw-r--r--   1 postgres postgres      313 Feb 17 16:36 .bashrc
> 
> Which in my ignorance leads me to believe that postgres will run in
> the bash shell and so I expect the use of arrow keys or command
> history.
> 
> Clues appreciated.
> 
> Peter
> 
> ---------------------------
> "Reality is that which, when you stop believing in it, doesn't go
> away".
>                 -- Philip K. Dick
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
--------------------------------------------------------------
Andrew J. Perrin - Programmer/Analyst, Desktop Support
Children's Primary Care Research Group, UNC - Chapel Hill
(919)966-9394 * andrew_perrin@unc.edu


Re: use of arrow keys to traverse history

From
Dorin Grunberg
Date:
You need to install Readline library. I know I used readline 4.1 and it 
works great.

Dorin


At 12:42 PM 4/25/2001 -0600, Peter J. Schoenster wrote:
>Hi,
>
>Not sure where this question belongs ... I thought postgresql was
>running under the bash shell where I can use up and down arrow
>keys to traverse my command history. I can do this in mysql but
>oddly not in oracle or postgresql.
>
>/home/postgres
>
>-rw-r--r--   1 postgres postgres     1422 Feb 16 15:50 .Xdefaults
>-rw-------   1 postgres postgres      458 Feb 17 16:59 .bash_history
>-rw-r--r--   1 postgres postgres       24 Feb 16 15:50 .bash_logout
>-rw-r--r--   1 postgres postgres      230 Feb 16 15:50 .bash_profile
>-rw-r--r--   1 postgres postgres      313 Feb 17 16:36 .bashrc
>
>Which in my ignorance leads me to believe that postgres will run in
>the bash shell and so I expect the use of arrow keys or command
>history.
>
>Clues appreciated.
>
>Peter
>
>---------------------------
>"Reality is that which, when you stop believing in it, doesn't go
>away".
>                 -- Philip K. Dick
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



RE: MySql 'REPLACE'

From
"Diehl, Jeffrey"
Date:
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
> 



Re: use of arrow keys to traverse history

From
Grant
Date:
I am finding that I can't use the cursor keys at all in psql after I
upgraded to v7.1. I have installed v7.1 on redhat 7.1, mandrake 7.2,
redhat 6.2 and none of them allow me to use the cursors keys, where as
v7.0.3 did. I haven't changed anything on the system except upgrade
postgresql to v7.1. Any ideas?

Thanks.

On Wed, 25 Apr 2001, Peter J. Schoenster wrote:

> Hi,
> 
> Not sure where this question belongs ... I thought postgresql was 
> running under the bash shell where I can use up and down arrow 
> keys to traverse my command history. I can do this in mysql but 
> oddly not in oracle or postgresql.  
> 
> /home/postgres
> 
> -rw-r--r--   1 postgres postgres     1422 Feb 16 15:50 .Xdefaults
> -rw-------   1 postgres postgres      458 Feb 17 16:59 .bash_history
> -rw-r--r--   1 postgres postgres       24 Feb 16 15:50 .bash_logout
> -rw-r--r--   1 postgres postgres      230 Feb 16 15:50 .bash_profile
> -rw-r--r--   1 postgres postgres      313 Feb 17 16:36 .bashrc
> 
> Which in my ignorance leads me to believe that postgres will run in 
> the bash shell and so I expect the use of arrow keys or command 
> history.
> 
> Clues appreciated.
> 
> Peter



Re: use of arrow keys to traverse history

From
Martín Marqués
Date:
On Jue 26 Abr 2001 01:08, Grant wrote:
> I am finding that I can't use the cursor keys at all in psql after I
> upgraded to v7.1. I have installed v7.1 on redhat 7.1, mandrake 7.2,
> redhat 6.2 and none of them allow me to use the cursors keys, where as
> v7.0.3 did. I haven't changed anything on the system except upgrade
> postgresql to v7.1. Any ideas?

That's because your version (talking about binaries) are not compiled with 
readline support. :-)

Saludos... :-)

-- 
El mejor sistema operativo es aquel que te da de comer.
Cuida tu dieta.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica                      Universidad Nacional
        del Litoral
 
-----------------------------------------------------------------


Re: use of arrow keys to traverse history

From
Grant
Date:
> That's because your version (talking about binaries) are not compiled with 
> readline support. :-)

Which binaries are not compiled with readline support? 

Why did this change from 7.0.3 -> 7.1?

Thanks.



Re: MySql 'REPLACE'

From
Thomas Swan
Date:
At 4/25/2001 07:38 AM, you wrote:<br /><blockquote cite="cite" class="cite" type="cite">I am working in porting the
Slashsoftware from MySql to PostgreSQL. I<br /> stepped in MySql's REPLACE command (a SQL command) that to my
knowledge<br/> is not supported by PostgreSQL and it's not standard SQL. According to<br /> MySql's manual:<br /><br />
"REPLACEworks exactly like INSERT, except that if an old record in the<br /> table has the same value as a new record
ona unique index, the old<br /> record is<br /> deleted before the new record is inserted. See section 7.21 INSERT<br
/>syntax."<br /><br /> REPLACE INTO table (column, column...) VALUES (value, value...)<br /><br /> Has anyone had any
experienceabout how to simulate it under PostgreSQL?<br /> I am using Perl and I can move most of the thing into
application<br/> anyway.</blockquote><br /> You should be able to do this with two separate queries inside a
transaction.<br/><br /> The only part I'm not clear on is whether to use an 'and' or an 'or' on the delete.  Check and
seeif 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'.<br /><br /> Give this a try...<br /><dl><dd>begin; <dd>delete from
tablewhere (column=value) and (column=value) and ...; <dd>insert into table (column, column...) values (value,
value...);<dd>end;<br /><br /></dl>From what you've given me, I think this would emulate that behavior.<br /><br />
Hopeit helps...<br /> 

Re: use of arrow keys to traverse history

From
Martín Marqués
Date:
On Jue 26 Abr 2001 01:16, Grant wrote:
> > That's because your version (talking about binaries) are not compiled
> > with readline support. :-)
>
> Which binaries are not compiled with readline support?
>
> Why did this change from 7.0.3 -> 7.1?

I don't have the slightest idea.
Are you on rpms? If so, download the src.rpm and check the SPEC file for the 
config options, and then compile with rpm --rebuild <package>

Saludos... :-)

-- 
El mejor sistema operativo es aquel que te da de comer.
Cuida tu dieta.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica                      Universidad Nacional
        del Litoral
 
-----------------------------------------------------------------


Re: MySql 'REPLACE'

From
Vivek Khera
Date:
>>>>> "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/