Search Postgresql Archives

select where not exists returning multiple rows?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux