select where not exists returning multiple rows?

From: Chris Dumoulin <chris(at)blaze(dot)io>
To: pgsql-general(at)postgresql(dot)org
Subject: select where not exists returning multiple rows?
Date: 2011-11-02 11:22:09
Message-ID: 4EB127E1.3090906@blaze.io
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

We're using postgresql 9.1, and we've got a table that looks like this:

testdb=# \d item
Table "public.item"
Column | Type | Modifiers
-------+----------+-----------
sig | bigint | not null
type | smallint |
data | text |
Indexes:
"item_pkey" PRIMARY KEY, btree (sig)

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. The idea is to insert
if the row doesn't already exist.
We're getting primary key constraint violations:

011-10-31 22:50:26 CDT STATEMENT: INSERT INTO Item (Sig, Type, Data)
SELECT $1,$2,$3 WHERE NOT EXISTS ( SELECT NULL FROM Item WHERE Sig=$4
FOR UPDATE)
2011-10-31 22:52:56 CDT ERROR: duplicate key value violates unique
constraint "item_pkey"
2011-10-31 22:52:56 CDT DETAIL: Key (sig)=(-4668668895560071572)
already exists.

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?

Thanks,
Chris

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2011-11-02 12:49:43 Re: select where not exists returning multiple rows?
Previous Message Graham Murray 2011-11-02 08:26:20 postgresql-9.1.1 PQescapeByteaConn() generating incorrectly quoted output.