Skip site navigation (1) Skip section navigation (2)

Re: ALTER TABLE ( smallinto -> boolean ) ...

From: Rod Taylor <pg(at)rbt(dot)ca>
To: "Marc G(dot) Fournier" <scrappy(at)postgresql(dot)org>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: ALTER TABLE ( smallinto -> boolean ) ...
Date: 2005-08-29 23:33:31
Message-ID: 1125358411.13034.103.camel@home (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
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;
-- 


In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-08-29 23:49:39
Subject: Re: ALTER TABLE ( smallinto -> boolean ) ...
Previous:From: David FetterDate: 2005-08-29 23:24:45
Subject: Re: ALTER TABLE ( smallinto -> boolean ) ...

pgsql-sql by date

Next:From: Tom LaneDate: 2005-08-29 23:49:39
Subject: Re: ALTER TABLE ( smallinto -> boolean ) ...
Previous:From: David FetterDate: 2005-08-29 23:24:45
Subject: Re: ALTER TABLE ( smallinto -> boolean ) ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group