"INSERT ... ON DUPLICATE KEY UPDATE ..." query.

Hi ,

I am trying to write a query in Postgres, for inserting values into a table , however if that INSERT were to insert a row with a duplicate key, it should update the original record in some manner., The duplicate key here is a foreign key so I am not sure how to check it out.

heres the table

*****************************************************
CREATE TABLE colorcode
(
  id int4 NOT NULL,
  tagid int4,
  logicid int4,
  paramvalue float8,
  CONSTRAINT colorcode_pkey PRIMARY KEY (id),
  CONSTRAINT colorcode_logicid_fkey FOREIGN KEY (logicid) REFERENCES logics (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT colorcode_tagid_fkey FOREIGN KEY (tagid) REFERENCES sites (id) ON UPDATE RESTRICT ON DELETE RESTRICT
)
***************************************************

If a row with a similar value of tagid exits, the Insert should update the logicid and the paramvalue for that record with the new values , instead of inserting a complete new record..

I was using something like this...which is giving me an error
"ERROR:  syntax error at or near "ON" at character 138"

*****************************************************
INSERT into colorcode (id,tagid,logicid,paramvalue)
SELECT CAST(nextval as integer),'3', '1','400'    
FROM nextval('colorcode_seq')
ON DUPLICATE Key UPDATE paramvalue = '400'
********************************************************

View the Solution FREE for 30 Days