Re: INSERT OR UPDATE?

Hello all,

I am writing an app in PHP that uses a PostGres database.
One thing i have noticed is that what should/could be a single line of
SQL code takes about 6 lines of PHP. This seem wasteful and redundant
to me.

Here is a sample of what I'm talking about ($db is a PDO already
defined and created).

$query[1] = "UPDATE my.table, SET somefield = '$someval' WHERE
somecondition";
$query[2] = "INSERT INTO my.table (somefield) VALUES ('$someval')";
if(!$db->query($query[1])){
$db->query($query[2]);
}

What I'm curious to know is if there is some way to simplify this,
either buy some PHP builtin or extension, or possibly something in SQL
I am missing. It seems to me that "UPDATE OR INSERT", should be valid,
but I can't seem to find anything relevant at all about it.

Anyways I hope you don't mind, but I'm crossposting this to
pgsql.general and comp.lang.php to see if I can get some information on
the subject.

I think is almost the same that in many other languages, and like in
many other with the time you can have function's libraries, or more
likely class libraries with the usefull stuff.

In desktop programming environments you have components, here you have
classes that are the same thing using it in another way. :) Watch out
the new auto-includes from PHP5 classes.

There are scripts with differents approaches, you can select one or
build your own for multiple proyects.
Personally I preffer not to build the SQL code in PHP. I allways have
the queries in XML files with an specific format I chose, to describe a
SQL Query with parameters. A class to handle the query can "tell me"
about the parameters or simply "receive" the parameters, build and
execute the query, and return the results or making it browseable
(recordset).

A very usual and smart approach is to use clases in PEAR::DB.

>I am writing an app in PHP that uses a PostGres database.
>One thing i have noticed is that what should/could be a single line of
>SQL code takes about 6 lines of PHP. This seem wasteful and redundant
>to me.
>
>Here is a sample of what I'm talking about ($db is a PDO already
>defined and created).
>
>$query[1] = "UPDATE my.table, SET somefield = '$someval' WHERE
>somecondition";
>$query[2] = "INSERT INTO my.table (somefield) VALUES ('$someval')";
>if(!$db->query($query[1])){
> $db->query($query[2]);
>}
>
>What I'm curious to know is if there is some way to simplify this,
>either buy some PHP builtin or extension, or possibly something in SQL
>I am missing. It seems to me that "UPDATE OR INSERT", should be valid,
>but I can't seem to find anything relevant at all about it.

MySQL permits (but it's not standard, and available in MySQL 4.1.0
and later):

INSERT INTO my.table (somefield) VALUES ('$someval') ON DUPLICATE
KEY UPDATE somefield = '$someval';

This is very useful for times when you want to count something (e.g.
SPAM), and if a record doesn't exist, make one with a count of 1.

I don't know whether something similar is available in PostGres.

Gordon L. Burditt

Gordon Burditt wrote:
> [...stuff snipped...]
>
> MySQL permits (but it's not standard, and available in MySQL 4.1.0
> and later):
>
> INSERT INTO my.table (somefield) VALUES ('$someval') ON DUPLICATE
> KEY UPDATE somefield = '$someval';
>
> This is very useful for times when you want to count something (e.g.
> SPAM), and if a record doesn't exist, make one with a count of 1.
>
> I don't know whether something similar is available in PostGres.
>
> Gordon L. Burditt

In Postgres you'd probably create a server-side function to get that
effect.

smorrey(at)gmail(dot)com writes:

> Hello all,
>
> I am writing an app in PHP that uses a PostGres database.
> One thing i have noticed is that what should/could be a single line of
> SQL code takes about 6 lines of PHP. This seem wasteful and redundant
> to me.
>
> Here is a sample of what I'm talking about ($db is a PDO already
> defined and created).

Well, at least you're using a very good DB!

This is easy to solve in Postgres.

Study up on "the rule system" and you will find the solution. A
BEFORE INSERT trigger could be used here as well.

create table foo (a int not mull primary key, b text);

create rule maybe_update as on insert to foo where exists (select 1
from foo where a = new.a) do instead update foo set b = new.b where a
= new.a;

Untested example above... but have done this sort of thing a lot. Can
be difficult to grasp at first.

A before insert trigger would test if the record exists already and if
so, do an update inside the trigger function and return null else
return new and the outter query proceed doing the insert.

HTH

--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/

smorrey(at)gmail(dot)com writes:

> Hello all,
>
> I am writing an app in PHP that uses a PostGres database.
> One thing i have noticed is that what should/could be a single line of
> SQL code takes about 6 lines of PHP. This seem wasteful and redundant
> to me.

Here ya go!...

create temp table foo (
id int primary key,
data text
);

create rule foo
as on insert to foo
where exists (
select 1
from foo
where id = new.id
)
do instead
update foo
set data = new.data
where id = new.id
;

copy foo from stdin using delimiters ',';
1,hello
2,hello
\.

select * from foo order by id;

insert into foo values (
1,'it works!'
);

select * from foo order by id;

Outout...

CREATE TABLE
CREATE RULE
id | data
----+-------
1 | hello
2 | hello
(2 rows)

INSERT 0 0
id | data
----+-----------
1 | it works!
2 | hello
(2 rows)

HTH

--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/

Try (for simple cases):

DELETE FROM my.table WHERE somecondition;
INSERT INTO my.table (somefield) VALUES ('$someval');

In complex cases it may be necessary to INSERT the values into a
temporary table, which is then used to condition the DELETE before
INSERTing the temporary table into your permanent table.

One advantage of the above is that it works for multiple rows, whereas
your technique does not.

-- Dean

On 2005-10-08 23:15, smorrey(at)gmail(dot)com wrote:

>Hello all,
>
>I am writing an app in PHP that uses a PostGres database.
>One thing i have noticed is that what should/could be a single line of
>SQL code takes about 6 lines of PHP. This seem wasteful and redundant
>to me.
>
>Here is a sample of what I'm talking about ($db is a PDO already
>defined and created).
>
>$query[1] = "UPDATE my.table, SET somefield = '$someval' WHERE
>somecondition";
>$query[2] = "INSERT INTO my.table (somefield) VALUES ('$someval')";
>if(!$db->query($query[1])){
> $db->query($query[2]);
>}
>
>What I'm curious to know is if there is some way to simplify this,
>either buy some PHP builtin or extension, or possibly something in SQL
>I am missing. It seems to me that "UPDATE OR INSERT", should be valid,
>but I can't seem to find anything relevant at all about it.
>
>Anyways I hope you don't mind, but I'm crossposting this to
>pgsql.general and comp.lang.php to see if I can get some information on
>the subject.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

On Sun, Oct 09, 2005 at 10:10:28AM -0400, Jerry Sievers wrote:
> smorrey(at)gmail(dot)com writes:
>
> > Hello all,
> >
> > I am writing an app in PHP that uses a PostGres database. One
> > thing i have noticed is that what should/could be a single line of
> > SQL code takes about 6 lines of PHP. This seem wasteful and
> > redundant to me.
>
> Here ya go!...
>
> create temp table foo (
> id int primary key,
> data text
> );
>
> create rule foo
> as on insert to foo
> where exists (
> select 1
> from foo
> where id = new.id
> )
> do instead
> update foo
> set data = new.data
> where id = new.id
> ;

This is very clever, but it has a race condition. What happens if
between the time of the EXISTS() check and the start of the UPDATE,
something happens to that row? Similarly, what if a row comes into
existence between the EXISTS() check and the INSERT?

The UPSERT example below, while a little more complicated to write and
use, handles this.

http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

SQL:2003 standard MERGE should fix all this.

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

On 09.10.2005 08:48, andrew wrote:
> A very usual and smart approach is to use clases in PEAR::DB.

Well, IMHO PEAR::DB is one of the worst classes of PEAR. Besides its
ugly code it's worth nothing. This is some incomplete "abstraction
layer" for kiddies, to make it easy for people coming from stupid
storage engines like MySQL or SqLite, to _store_ their data in another
DBMS.

I never really understood, what such a thing should be good for. SQL
implementations are not portable, nor are stored procedures, functions,
rules, triggers, etc. Every DBMS implements them in it's own way and
almost all professional devs will implement most, or at least the
critical parts, of the business logic directly inside DBMS and not at
application level. Thus making the application basically just a
frontend, calling stored procedures and writing to views, controlled by
triggers and strict permissions.

The only thing PEAR::DB might be usefull for is, writing those famous
cross-dbms-apps, which basically use the DBMS highly inefficient as a
better plaintext file (not even using foreign keys in most cases, so it
will work with MySql 3.x as well).

And if you don't what to write such bizarre cross-dbms-apps, what is the
point in using PEAR::DB at all? Those few incomplete methods wrapping
the pg_* functions in some highly debatable way?

Do yourself a favor and write your own slime and efficient set of
classes around the native API-functions of your DBMS. PEAR::DB and
others prevent you from using an advanced DBMS the way you should.

--
Regards,
Hannes Dorbath

Actually I think the uniqueness constraint (due to the primary key) is the
one handling it. There's nothing special about that function that will
prevent duplicates.

Try running it without the primary key specifications in two separate
concurrent transactions. Then commit both transactions.

Similarly the other methods will be fine as long as there is a uniqueness
constraint.

If you don't have a uniqueness constraint or you don't want to trigger and
exception/error (which could be troublesome in versions of Postgresql
without savepoints) then you will have to use locking.

It's actually quite surprising how many people get this wrong and don't
realize it (I wonder how many problems are because of this). The SQL spec
should have had a PUT/MERGE decades ago. The insert vs update format being
different is also annoying, oh well.

Regards,
Link.

At 10:01 AM 10/9/2005 -0700, David Fetter wrote:
>This is very clever, but it has a race condition. What happens if
>between the time of the EXISTS() check and the start of the UPDATE,
>something happens to that row? Similarly, what if a row comes into
>existence between the EXISTS() check and the INSERT?
>
>The UPSERT example below, while a little more complicated to write and
>use, handles this.
>
>http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
>
>SQL:2003 standard MERGE should fix all this.
>
>Cheers,
>D
>--
>David Fetter david(at)fetter(dot)org http://fetter.org/
>phone: +1 510 893 6100 mobile: +1 415 235 3778
>
>Remember to vote!
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: don't forget to increase your free space map settings

On Mon, 2005-10-10 at 13:34, Lincoln Yeoh wrote:
[snip]
> It's actually quite surprising how many people get this wrong and don't
> realize it (I wonder how many problems are because of this). The SQL spec
> should have had a PUT/MERGE decades ago. The insert vs update format being
> different is also annoying, oh well.

Referring to the above, is there any plan to implement such commands in
postgres ? I don't know if it is standard SQL, but some other RDBMSes
have such command, and they are actually useful.

>
> Regards,
> Link.
[snip]

Cheers,
Csaba.

Check the TODO, I'm 99% certain it's on there.

On Mon, Oct 10, 2005 at 02:02:32PM +0200, Csaba Nagy wrote:
> On Mon, 2005-10-10 at 13:34, Lincoln Yeoh wrote:
> [snip]
> > It's actually quite surprising how many people get this wrong and don't
> > realize it (I wonder how many problems are because of this). The SQL spec
> > should have had a PUT/MERGE decades ago. The insert vs update format being
> > different is also annoying, oh well.
>
> Referring to the above, is there any plan to implement such commands in
> postgres ? I don't know if it is standard SQL, but some other RDBMSes
> have such command, and they are actually useful.
>
> >
> > Regards,
> > Link.
> [snip]
>
> Cheers,
> Csaba.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461