Обсуждение: Insert Ignore or something similar...
Hello everybody,
I've just wondered if there are any way to implement an INSERT IGNORE in PostgreSQL, I know, I could catch an exception in PL/pgSQL and just handle it in the right way, but I would need to write a SP for the task. I've been lookin into the documentation and I found no similar INSERT IGNORE.
Thanks a lot!
I don't think any such behavior exists in PostgreSQL, and based on a reading of the behavior in MySQL, I can't imagine it ever existing considering the preference of PostgreSQL developers for correct (and sane) behavior. INSERT IGNORE seems like a foot-cannon...
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)On Sep 5, 2005, at 5:20 PM, Cristian Prieto wrote:
Hello everybody,I've just wondered if there are any way to implement an INSERT IGNORE in PostgreSQL, I know, I could catch an exception in PL/pgSQL and just handle it in the right way, but I would need to write a SP for the task. I've been lookin into the documentation and I found no similar INSERT IGNORE.Thanks a lot!
On Mon, Sep 05, 2005 at 10:35:49PM -0500, Thomas F. O'Connell wrote: > I don't think any such behavior exists in PostgreSQL, and based on a > reading of the behavior in MySQL, I can't imagine it ever existing > considering the preference of PostgreSQL developers for correct (and > sane) behavior. INSERT IGNORE seems like a foot-cannon... What is it supposed to do? If ignoring errors is the behavior Cristian wants, it's possible to do with savepoints (or PL/pgSQL exceptions). -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com "People get annoyed when you try to debug them." (Larry Wall)
On Sep 5, 2005, at 10:51 PM, Alvaro Herrera wrote: > On Mon, Sep 05, 2005 at 10:35:49PM -0500, Thomas F. O'Connell wrote: > >> I don't think any such behavior exists in PostgreSQL, and based on a >> reading of the behavior in MySQL, I can't imagine it ever existing >> considering the preference of PostgreSQL developers for correct (and >> sane) behavior. INSERT IGNORE seems like a foot-cannon... >> > > What is it supposed to do? If ignoring errors is the behavior > Cristian > wants, it's possible to do with savepoints (or PL/pgSQL exceptions). Yeah, I think savepoints might be his best bet. It sounded like he was explicitly trying to avoid PL/pgSQL. Cristian, here's a pointer to the SAVEPOINT docs: http://www.postgresql.org/docs/8.0/static/sql-savepoint.html I was mostly remarking that I couldn't envision a flipped switch for incorrect/non-standard INSERT behavior. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax)
Thanks a lot! Well, I just want to avoid a begin...exception when... end block in plpgsql, just do it in a few lines of code without a sp... Thanks a lot again! ----- Original Message ----- From: "Thomas F. O'Connell" <tfo@sitening.com> To: "Alvaro Herrera" <alvherre@alvh.no-ip.org> Cc: "Cristian Prieto" <cristian@clickdiario.com>; <pgsql-general@postgresql.org> Sent: Monday, September 05, 2005 10:01 PM Subject: Re: [GENERAL] Insert Ignore or something similar... On Sep 5, 2005, at 10:51 PM, Alvaro Herrera wrote: > On Mon, Sep 05, 2005 at 10:35:49PM -0500, Thomas F. O'Connell wrote: > >> I don't think any such behavior exists in PostgreSQL, and based on a >> reading of the behavior in MySQL, I can't imagine it ever existing >> considering the preference of PostgreSQL developers for correct (and >> sane) behavior. INSERT IGNORE seems like a foot-cannon... >> > > What is it supposed to do? If ignoring errors is the behavior Cristian > wants, it's possible to do with savepoints (or PL/pgSQL exceptions). Yeah, I think savepoints might be his best bet. It sounded like he was explicitly trying to avoid PL/pgSQL. Cristian, here's a pointer to the SAVEPOINT docs: http://www.postgresql.org/docs/8.0/static/sql-savepoint.html I was mostly remarking that I couldn't envision a flipped switch for incorrect/non-standard INSERT behavior. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
On Tue, Sep 06, 2005 at 08:55:26AM -0600, Cristian Prieto wrote: > Thanks a lot! > > Well, I just want to avoid a begin...exception when... end block in > plpgsql, just do it in a few lines of code without a sp... SAVEPOINT foo; INSERT ... ; if it fails ROLLBACK TO foo; else RELEASE foo This is pretty much the same that plpgsql begin/exception/end does. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com "La conclusión que podemos sacar de esos estudios es que no podemos sacar ninguna conclusión de ellos" (Tanenbaum)