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(at)albourne(dot)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 Power To Serve
--------------------------------------------------------------------

/* We prefer linguistic vigor over mathematical rigor. */

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...

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(at)albourne(dot)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 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(at)unl(dot)edu(dot)ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------

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(at)postgresql(dot)org

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

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

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(at)postgresql(dot)org

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

> 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.

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(at)unl(dot)edu(dot)ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------