Re: Unable to Update a Record

Wang, Mary Y wrote:
> I tried to do (3) as well for reindex.
> But I got this error:
>
> reindex table users;
> ERROR: Cannot create unique index. Table contains non-unique values.
>
> Do you know what does this mean?

Just what it says. Somehow your table has got corrupted, possibly with
an old and a new version of the same row available.

Take a pg_dump of the entire database (for backup), and then you'll want
to search for the duplicates. Something like:

SELECT user_id,count(*) FROM users GROUP BY user_id HAVING count(*) > 1;

Or, to see actual rows:

SELECT oid,* FORM users WHERE user_id IN (
SELECT user_id
FROM users
GROUP BY user_id
HAVING count(*) > 1
);

Then, you can delete them via their OID.

The question is - how did your table get this problem. Check the
release-notes for versions more recent than yours and see if anything
looks relevant:
http://www.postgresql.org/docs/8.0/static/release.html

Have you had any crashes?
--
Richard Huxton
Archonet Ltd