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
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. |