[SQL] Unexpected SQL error for UPDATE
Originally I have a table like thiscreate table users ( userid integer not null, email_address varchar (255) unique not null,PRIMARY KEY (userid) ); Later I find that varchar(255) is not enough. I designed to change the type totext. There is no simple SQL to alter the type. So I use a series of SQLs tocreate a new column, copy the data over and then replace the old column with the new. ALTER TABLE users ADD email_address_text text UNIQUE; UPDATE users set email_address_text=email_address;ALTER TABLE users DROP email_address;ALTER TABLE users RENAME email_address_text TO email_address;ALTER TABLE users ALTER email_address SET not null; This works mostly OK. Until I have one database that has over 1 million recordsin table user. It fails with an inexplicable error:mydb=# UPDATE users set email_address_text=email_address;ERROR: invalid page header in block 6776 of relation "users_email_address_text_key" Anyone can shred some light what has went wrong?wy
Re: [SQL] Unexpected SQL error for UPDATE
Yes that helped! reindex index users_email_address_text_key; UPDATE users set email_address_text=email_address;It kind of works now. Or at least it does not result in error. The UPDATE query is running for more than an hour and still hasn't return. This is inline with what we have seen before that coping 1 million fields seems to be excessively slow. About the index, it is created implicitly because of the UNIQUE constraint. At this point right after the column is added, they are all null. wyOn 7/12/06, Jaime Casanova <[EMAIL PROTECTED]> wrote: users_email_address_text_key sounds a lot like an index... is it anindex? if so, what happenned if you REINDEX the table or even DROP andCREATE the index again? it certainly sounds to an index corruption.-- regards,Jaime Casanova
