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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
