In-Reply-To: <cbd177510607111747h7542382bke12a6fc152338fe6@mail.gmail.com>
References: <cbd177510607111747h7542382bke12a6fc152338fe6@mail.gmail.com>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <793EC6C5-19C6-490F-812A-AD9FD0070107@seespotcode.net>
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: Unexpected SQL error for UPDATE
Date: Wed, 12 Jul 2006 08:18:30 -0400
To: aurora <aurora00@gmail.com>


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