Re: Duplicated entries are not ignored even if a "do instead nothing" rule is added.

[邓尧 <torshie@xxxxxxxxx>]

On Mon, Jan 9, 2012 at 9:53 AM, Jon Nelson <jnelson+pgsql@xxxxxxxxxxx> wrote:

On Sun, Jan 8, 2012 at 7:13 PM, 邓尧 <torshie@xxxxxxxxx> 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