Re: Unexpected SQL error for UPDATE

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