This question has been flagged

4 down vote favorite

Here is the weird problem i am facing. I am trying to enter data using following query

insert into product_product (id, product_tmpl_id, make_equip, model_equip, name_template, serial_num_equip, location_equip, issue_date_equip, issue_to_equip, remarks_equip, pr, ch, categ_id,valuation) values (700,700,'Nikon','Action 10x50 Lookout','Nikon Action 10x50 Lookout','671386','40 Wall St.','5/13/2004 12:00:00 AM','','OM''s OFFICE',62,72,502,'manual periodic');

I get the error :

ERROR: duplicate key value violates unique constraint "product_product_pkey" DETAIL: Key (id)=(700) already exists. ********** Error ********** ERROR: duplicate key value violates unique constraint "product_product_pkey" SQL state: 23505 Detail: Key (id)=(700) already exists.

I ran select query on that record like this:

select * from product_product where id=700

It returns columns with no data(data includes id too)

I tried to run update query like this:

update product_product set product_tmpl_id=700, make_equip='Nikon', model_equip='Action 10x50 Lookout', name_template='Nikon Action 10x50 Lookout', serial_num_equip='671386', location_equip='40 Wall St.', issue_date_equip='5/13/2004 12:00:00 AM', issue_to_equip='', remarks_equip='OM''s OFFICE', pr=62, ch=72, categ_id=502,valuation='manual periodic' where id=700;

result was:

Query returned successfully: 0 rows affected, 1 ms execution time.

i tried delete query too but same output.

Also show all data shows nothing like id 700.

I don't know why this is happening.

I re indexed and Vacuumed my table from maintenance but same result.

Plz let me know what is wrong with it.

Thanks

Edit

here is my create table

CREATE TABLE product_product ( id serial NOT NULL, create_uid integer, create_date timestamp without time zone, write_date timestamp without time zone, write_uid integer, ean13 character varying(13), -- EAN13 Barcode color integer, -- Color Index image bytea, -- Image price_extra numeric, -- Variant Price Extra default_code character varying(64), -- Internal Reference name_template character varying(128), -- Template Name ..... description_sale text, procure_method character varying, produce_delay double precision, uom_id integer, name character varying, office_equip character varying, -- Office(Equipment) lease_agreement_equip character varying, -- Lease Agreement reg_exp_date_equip character varying, -- Registration Expiration Date CONSTRAINT product_product_pkey PRIMARY KEY (id ), CONSTRAINT product_product_categ_temp2_fkey FOREIGN KEY (categ_temp2) REFERENCES product_category (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL, CONSTRAINT product_product_categ_temp3_fkey FOREIGN KEY (categ_temp3) REFERENCES product_category (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL, CONSTRAINT product_product_categ_temps_fkey FOREIGN KEY (categ_temps) REFERENCES product_category (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL, CONSTRAINT product_product_categg_temp_fkey FOREIGN KEY (categg_temp) REFERENCES product_category (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL, CONSTRAINT product_product_ch_fkey FOREIGN KEY (ch) REFERENCES product_category (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL, CONSTRAINT product_product_create_uid_fkey FOREIGN KEY (create_uid) REFERENCES res_users (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL, CONSTRAINT product_product_my_products_fkey FOREIGN KEY (my_products) REFERENCES product_product (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL, CONSTRAINT product_product_phone_data_id_fkey FOREIGN KEY (phone_data_id) REFERENCES phone_datas (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL, CONSTRAINT product_product_phone_id_fkey FOREIGN KEY (phone_id) REFERENCES phone_types (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL, CONSTRAINT product_product_pr_fkey FOREIGN KEY (pr) REFERENCES product_category (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL, CONSTRAINT product_product_product_tmpl_id_fkey FOREIGN KEY (product_tmpl_id) REFERENCES product_template (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE, CONSTRAINT product_product_subchild_fkey FOREIGN KEY (subchild) REFERENCES product_category (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL, CONSTRAINT product_product_temp_id_fkey FOREIGN KEY (temp_id) REFERENCES product_category (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL, CONSTRAINT product_product_write_uid_fkey FOREIGN KEY (write_uid) REFERENCES res_users (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE SET NULL ) WITH ( OIDS=FALSE );

I have something like 200+ columns, so i have removed them from here....

Avatar
Discard
Best Answer

Hi Saghir,

This is highly unusual.  Based on all the info provided it seems the index and/or data has internal corruption (not usual by any means).

Try this:

1. Drop the index and then recreate it. They try your insert again.

2. If still no good, drop the table (export the data first!) and index and re-create it and reload the data.

Some other things you can check:
Are you out of disk space?  That causes all sorts of odd occurences.
Are you actually doing the insert twice by mistake? (doesn't seem like, from what you shown, but still)

Hope that helps.

 

Avatar
Discard