Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: INSERT OR UPDATE?

Re: INSERT OR UPDATE?

From
Jerry Sievers
Date:
smorrey@gmail.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/

Re: INSERT OR UPDATE?

From
David Fetter
Date:
On Sun, Oct 09, 2005 at 10:10:28AM -0400, Jerry Sievers wrote:
> smorrey@gmail.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@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Re: INSERT OR UPDATE?

From
Lincoln Yeoh
Date:
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@fetter.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

Re: INSERT OR UPDATE?

From
Csaba Nagy
Date:
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.

Re: INSERT OR UPDATE?

From
"Jim C. Nasby"
Date:
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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461