Is there a postgresql SQL idiom to perform an UPDATE, which becomes an
INSERT if the primary key does not exist?

I'm not sure I *should* use it in my application, I just want to know if
it can be done. Thanks.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo [at] postgresql.org)

On Thu, Jul 15, 2004 at 13:20:57 -0500,
Jeff Kowalczyk <jtk [at] yahoo.com> wrote:
> Is there a postgresql SQL idiom to perform an UPDATE, which becomes an
> INSERT if the primary key does not exist?
>
> I'm not sure I *should* use it in my application, I just want to know if
> it can be done. Thanks.

There isn't a single statement that does this.

This has been discussed a number of times. The archives will have some
different examples.

You end up needing to either lock the table or check for failure since
postgres doesn't have predicate locking. Which technique is best will
depend on the details of your situation.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

--cw81t83UHTonwpSu
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* Jeff Kowalczyk (jtk [at] yahoo.com) wrote:
> Is there a postgresql SQL idiom to perform an UPDATE, which becomes an
> INSERT if the primary key does not exist?
>
> I'm not sure I *should* use it in my application, I just want to know if
> it can be done. Thanks.

Unfortunately, I don't believe there's one in PostgreSQL yet. MERGE is
similar to this, and is defined in the latest SQL spec. Hopefully it'll
be implemented in PostgreSQL sometime soon.

Stephen

--cw81t83UHTonwpSu
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFA9sOrrzgMPqB3kigRAq6sAJ0fF1pN4TbFPcpnwJ9WrXROjDTh+wCg jO9x
dunf06OtCwOPnLNIbeo5O0E=
=gtk5
-----END PGP SIGNATURE-----

--cw81t83UHTonwpSu--