Re: select where not exists returning multiple rows?

On Wed, Nov 02, 2011 at 07:22:09AM -0400, Chris Dumoulin wrote:
> And we're doing an insert like this:
> INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS
> ( SELECT NULL FROM Item WHERE Sig=$4)
>
> In this case $1 and $4 should always be the same.

FWIW, If they're always going to be the same, you can put that it the query,
like so:

INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS
( SELECT NULL FROM Item WHERE Sig=$1)

Saves a parameter.

> I don't see how it's possible to get duplicate rows here, unless
> maybe the "select where not exists" is somehow returning multiple
> rows.
> Any ideas what's going on here?

As pointed out by others, you don't say if it this is a race condition
between processes or if it always does this.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer