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-01 14:59:41 |
Message-ID: | 4EB0095D.10200@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
From | Date | Subject | |
---|---|---|---|
Next Message | Debasis Mishra | 2011-11-01 15:00:10 | Re: Regarding the shared disk fail over cluster configuration |
Previous Message | Tom Lane | 2011-11-01 13:43:00 | Re: Performance Problem with postgresql 9.03, 8GB RAM, Quadcore Processor Server--Need help!!!!!!! |