Thread: 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
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. */
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
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
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
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
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 >
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
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
-----------------------------------------------------------------
> 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.
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 />
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
-----------------------------------------------------------------
>>>>> "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/