It sounds to me like the index is still referencing the old table, not the NEW one that you just copied over. That's why it's wack. Not to worry, nothing is broken; messing with indexes is usually pretty safe. There are several things we could do in this case.
First, here's the REINDEX command:
http://www.commandprompt.com/ppbook/r27795.htm
Here's an excerpt from the page, see the link above for a full description:
Quote:
Synopsis
REINDEX { TABLE | DATABASE | INDEX } name [ FORCE ]
Description
Use the REINDEX command to rebuild any indices that have become corrupt. This is especially useful if system indices become corrupted. To fix them, shutdown postmaster and start it using the -o "-O -P" command-line parameter. This opens a standalone server that allows for re-indexing of system indices. Run the REINDEX DATABASE command once you are at the psql prompt.
|
I am assuming in this case your command would be something like :
Quote:
REINDEX INDEX profiles_pkey;
|
But notice from the description above, you'll need to shut down Postgres and restart it in standalone mode, before running that command.
Usually I just drop and recreate indexes, unless they are insanely big, since it's usually pretty safe to drop them. But since I don't know if your index is a special kind of index, (like if it's a function based index) try out the REINDEX command above and let me know how it works out. If it doesn't then we can consider just dropping the thing and recreating it.
Some people even drop and recreate indexes now and then as a matter of performance tuning. (For example if you have a table with a alot of deletions in it, the index may not be optimized for that table).
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Thanks
Linear Mode
