On Jul 11, 2006, at 20:47 , aurora wrote:
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.
What version of PostgreSQL are you running? In 8.0 and later you can
use ALTER TABLE ALTER TYPE.
http://www.postgresql.org/docs/current/interactive/sql-altertable.html
Something like this (untested) should work in your case:
ALTER TABLE users ALTER email_addresses TYPE text;
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 looks like it should work, so perhaps someone else has an idea
as to why you're receiving the error. Is the error reproducible? If
you can put together a reproducible test case, it may help someone
else be able to figure out what's going on.
Michael Glaesemann
grzm seespotcode net
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
