From: aurora <aurora00@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Unexpected SQL error for UPDATE
Date: Tue, 11 Jul 2006 17:47:31 -0700
Message-ID: <cbd177510607111747h7542382bke12a6fc152338fe6@mail.gmail.com> (raw)
Originally I have a table like this
create 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
to
text. There is no simple SQL to alter the type. So I use a series of SQLs to
create 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
records
in 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: aurora00@gmail.com
Subject: Re: Unexpected SQL error for UPDATE
In-Reply-To: <cbd177510607111747h7542382bke12a6fc152338fe6@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox