From: Rod Taylor <pg@rbt.ca>
To: "Marc G. Fournier" <scrappy@postgresql.org>
Cc: pgsql-hackers@postgresql.org
In-Reply-To: <20050829201253.X1044@ganymede.hub.org>
References: <20050829201253.X1044@ganymede.hub.org>
Date: Mon, 29 Aug 2005 19:33:31 -0400
Message-Id: <1125358411.13034.103.camel@home>
Mime-Version: 1.0
Subject: Re: ALTER TABLE ( smallinto -> boolean ) ...
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Mon, 2005-08-29 at 20:15 -0300, Marc G. Fournier wrote:
> I have a table with several 'smallint' fields that I'd like to convert to 
> booleean ... the data in each is either 0 or 1, and:
> 
> # ALTER TABLE table ALTER COLUMN field1 type boolean;
> ERROR:  column "field1" cannot be cast to type "pg_catalog.bool"
> 
> Should this not work?  If not, is there a way to do it so that it will, 
> without having to reload the whole table?

development=# select '0'::smallint::boolean;
ERROR:  cannot cast type smallint to boolean

You were casting an unknown to boolean.

Anyway, USING is what you're looking for:

ALTER TABLE table
	ALTER COLUMN field1 TYPE boolean
		USING CASE WHEN field1 = 0 THEN FALSE
			WHEN field1 = 1 THEN TRUE
			ELSE NULL
			END;
--