On 4 December 2011 12:32, Phoenix Kiula <phoenix.ki...@gmail.com> wrote:
> Hi. > > I have deleted a row from a table. Confirmed by "SELECT". All > associated children tables don't have this key value either. > > Yet, when I insert this row back again, the primary key index on this > table gives me a duplicate error. > > As demonstrated below. PGSQL version is 9.0.5. > > Is this common? I have vacuum analyzed the table three times. Still > same problem. Why is the primary key index keeping a value that was > deleted? > > Short of a REINDEX (which will lock the entire table....it's a large > one) is there anything I can do to clear up the index? > > Thanks! > > > > mydb=# delete from stores where id = '20xrrs3'; > DELETE 0 > Time: 0.759 ms > > mydb=# INSERT INTO stores (id) VALUES ('20xrrs3'); > mydb-# > ERROR: duplicate key value violates unique constraint "idx_stores_pkey" > DETAIL: Key (id)=(20xrrs3) already exists. > mydb=# > mydb=# > > Hi, could you run the following queries and show us the results? SELECT count(*) FROM stores WHERE id = '20xrrs3'; delete from stores where id = '20xrrs3'; SELECT count(*) FROM stores WHERE id = '20xrrs3'; and then show us the whole table structure, especially any rules or triggers. regards Szymon -- *http://simononsoftware.com/* <http://simononsoftware.com/>