From: Michael Glaesemann <grzm@seespotcode.net>
To: aurora <aurora00@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Unexpected SQL error for UPDATE
Date: Wed, 12 Jul 2006 08:18:30 -0400
Message-ID: <793EC6C5-19C6-490F-812A-AD9FD0070107@seespotcode.net> (raw)
In-Reply-To: <cbd177510607111747h7542382bke12a6fc152338fe6@mail.gmail.com>
References: <cbd177510607111747h7542382bke12a6fc152338fe6@mail.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
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: grzm@seespotcode.net, aurora00@gmail.com
Subject: Re: Unexpected SQL error for UPDATE
In-Reply-To: <793EC6C5-19C6-490F-812A-AD9FD0070107@seespotcode.net>
* 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