Re: Duplicated entries are not ignored even if a "do instead nothing" rule is added. - Mailing list pgsql-general

From 邓尧
Subject Re: Duplicated entries are not ignored even if a "do instead nothing" rule is added.
Date
Msg-id CAOb3iuh9GeJQGNPSXCv8YCjpfL8L2VrXrq+yeeQ18+C-ryfioQ@mail.gmail.com
Whole thread Raw
In response to Re: Duplicated entries are not ignored even if a "do instead nothing" rule is added.  (Jon Nelson <jnelson+pgsql@jamponi.net>)
List pgsql-general

On Mon, Jan 9, 2012 at 9:53 AM, Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
On Sun, Jan 8, 2012 at 7:13 PM, 邓尧 <torshie@gmail.com> wrote:
> I have enabled the autocommit feature of psycopg2, and removed all the
> transactions in source code, also changed the sql statement to the
> following:
>
> insert into ACCOUNT(HOME)
>     select "v1" as HOME
>     where not exists (select 1 from ACCOUNT where HOME = "v1")
>
> Surprisingly, I still got the unique constraint violation error, but much
> fewer than before ( only 3 during the last 3 days).
> Any suggestions? Could it be a bug in psycopg2 or postgresql?

Presumably, you need to add a distinct.
insert into ... select DISTINCT .... where not exists.
 
I don't think so. The the statement "select .. as .. where not exists" will never return more than one row.
 


--
Jon

pgsql-general by date:

Previous
From: Damiano ALBANI
Date:
Subject: Re: Supporting SQL/MED DATALINK
Next
From: Stefan Keller
Date:
Subject: Re: Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues