upsert functionality
Hi,
Our company is planning to move to postreSQL. We were initially using sybase where upsert functionality was available using "insert on existing update" clause. I know there multiple ways to fix this using RULE or separate function in postgresql. But I would like to know which version of postgresql has support for upsert planned using an official syntax. I have postgresql 9.2 which does not have this feature, if its planned in near future, I would rather wait to migrate to PostgreSQL.
Thanks,
Sajeev
Sajeev Mayandi, 16.05.2013 07:01:
Hi,
Our company is planning to move to postreSQL. We were initially using
sybase where upsert functionality was available using "insert on
existing update" clause. I know there multiple ways to fix this
using RULE or separate function in postgresql. But I would like to
know which version of postgresql has support for upsert planned using
an official syntax. I have postgresql 9.2 which does not have this
feature, if its planned in near future, I would rather wait to
migrate to PostgreSQL.
You can use writeable CTEs for this purpose.
There are several examples out there:
http://www.xzilla.net/blog/2011/Mar/Upserting-via-Writeable-CTE.html
http://www.depesz.com/2011/03/16/waiting-for-9-1-writable-cte/
http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/
http://stackoverflow.com/a/8702291/330315
Regards
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On May 15, 2013, at 11:52 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
Sajeev Mayandi, 16.05.2013 07:01:
Hi,
Our company is planning to move to postreSQL. We were initially using
sybase where upsert functionality was available using "insert on
existing update" clause. I know there multiple ways to fix this
using RULE or separate function in postgresql. But I would like to
know which version of postgresql has support for upsert planned using
an official syntax. I have postgresql 9.2 which does not have this
feature, if its planned in near future, I would rather wait to
migrate to PostgreSQL.You can use writeable CTEs for this purpose.
There are several examples out there:
http://www.xzilla.net/blog/2011/Mar/Upserting-via-Writeable-CTE.html
http://www.depesz.com/2011/03/16/waiting-for-9-1-writable-cte/
http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/
One thing I didn't see mentioned in two of the links -- they mention race
conditions, where multiple writers can still cause the faked UPSERT to fail.
This can be avoided using SERIALIZABLE transactions, now that Postgres has
SSI. http://wiki.postgresql.org/wiki/SSI
I can vouch that we use writable CTEs and SERIALIZABLE to implement UPSERT
in production with no issues thus far.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thank you for true response will try out.
Sajeev
On 5/16/13 10:27 AM, "Steven Schlansker" <steven@likeness.com> wrote:
On May 15, 2013, at 11:52 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
Sajeev Mayandi, 16.05.2013 07:01:
Hi,
Our company is planning to move to postreSQL. We were initially using
sybase where upsert functionality was available using "insert on
existing update" clause. I know there multiple ways to fix this
using RULE or separate function in postgresql. But I would like to
know which version of postgresql has support for upsert planned using
an official syntax. I have postgresql 9.2 which does not have this
feature, if its planned in near future, I would rather wait to
migrate to PostgreSQL.You can use writeable CTEs for this purpose.
There are several examples out there:
http://www.xzilla.net/blog/2011/Mar/Upserting-via-Writeable-CTE.html
http://www.depesz.com/2011/03/16/waiting-for-9-1-writable-cte/
http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/One thing I didn't see mentioned in two of the links -- they mention race
conditions, where multiple writers can still cause the faked UPSERT to
fail.This can be avoided using SERIALIZABLE transactions, now that Postgres has
SSI. http://wiki.postgresql.org/wiki/SSII can vouch that we use writable CTEs and SERIALIZABLE to implement UPSERT
in production with no issues thus far.--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Some days ago, i had to to implement an upsert and used a a /LOCK TABLE/ to
prevent the race conditions. Working fine for the moment.
http://www.joocom.de/blog/postgresql-insert-und-update-in-einem-statement/
<http://www.joocom.de/blog/postgresql-insert-und-update-in-einem-statement/>
--
View this message in context: http://postgresql.1045698.n5.nabble.com/upsert-functionality-tp5755712p5788365.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 1/22/2014 10:21 AM, joocom wrote:
Some days ago, i had to to implement an upsert and used a a/LOCK TABLE/ to
prevent the race conditions. Working fine for the moment.http://www.joocom.de/blog/postgresql-insert-und-update-in-einem-statement/
<http://www.joocom.de/blog/postgresql-insert-und-update-in-einem-statement/>
that won't perform very well if you have a high level of concurrency.
have you seen the upsert example in the postgres plpgsql docs ?
--
john r pierce 37N 122W
somewhere on the middle of the left coast