Thread: Duplicate Key Violates Unique Contraint when Updating a table
Hi all, I am experiencing thgis problem since this morning. I seldom heard about unique key constraint violation to happen when updating a table. It happens to 1 or few records at early stage, but then to all. My updating command is as following: update mytable set my_status='Y' where myid='ABC123567778'; Can anyone help? All helps and solutions are appreciated. THanks. CK _________________________________________________________________ Check it out! Windows Live Spaces is here! http://spaces.live.com/?mkt=en-sg It�s easy to create your own personal Web site.
On sun, 2007-06-24 at 16:55 +0800, carter ck wrote: > I am experiencing thgis problem since this morning. I seldom heard about > unique key constraint violation to happen when updating a table. > > It happens to 1 or few records at early stage, but then to all. > > My updating command is as following: > > update mytable set my_status='Y' where myid='ABC123567778'; > > Can anyone help? All helps and solutions are appreciated. sounds like you have a UNIQUE constraint involving the column "my_status". did you recently create a new index on this table? what does psql say to: \d mytable gnari
Hi.. No. The only unique key is the myid, which is also the primary key. I have extracted the records out from this database and dump all to a new database. Everything is fine. But, this is the second time I have encountered this problem. Hopefully those who have solutions for this can kindly post a feedback. THanks. >From: Ragnar <gnari@hive.is> >To: carter ck <carterck32@hotmail.com> >CC: pgsql-general@postgresql.org >Subject: Re: [GENERAL] Duplicate Key Violates Unique Contraint whenUpdating >a table >Date: Sun, 24 Jun 2007 10:05:16 +0000 > >On sun, 2007-06-24 at 16:55 +0800, carter ck wrote: > > I am experiencing thgis problem since this morning. I seldom heard about > > unique key constraint violation to happen when updating a table. > > > > It happens to 1 or few records at early stage, but then to all. > > > > My updating command is as following: > > > > update mytable set my_status='Y' where myid='ABC123567778'; > > > > Can anyone help? All helps and solutions are appreciated. > >sounds like you have a UNIQUE constraint involving the column >"my_status". > >did you recently create a new index on this table? > >what does psql say to: >\d mytable > > >gnari > > _________________________________________________________________ Get the new Windows Live Messenger! http://get.live.com/messenger/overview
On sun, 2007-06-24 at 17:58 +0800, carter ck wrote: > >From: Ragnar <gnari@hive.is> > >On sun, 2007-06-24 at 16:55 +0800, carter ck wrote: > > > I am experiencing thgis problem since this morning. I seldom heard about > > > unique key constraint violation to happen when updating a table. > > > > > > It happens to 1 or few records at early stage, but then to all. > > > > > > My updating command is as following: > > > > > > update mytable set my_status='Y' where myid='ABC123567778'; > > > > > > Can anyone help? All helps and solutions are appreciated. > > > >sounds like you have a UNIQUE constraint involving the column > >"my_status". > > > >did you recently create a new index on this table? > > > >what does psql say to: > >\d mytable > > > > No. The only unique key is the myid, which is also the primary key. I have > extracted the records out from this database and dump all to a new database. > Everything is fine. But, this is the second time I have encountered this > problem. do you still have the old database around? if you do, does a REINDEX mytable; make your problem go away? gnari