Thread: ALTER TABLE ( smallinto -> boolean ) ...
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: # select '1'::boolean; bool ------ t (1 row) # select '0'::boolean; bool ------ f (1 row) so they do cast as expected ... but, if I try to do the ALTER, I get: # 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? Thanks ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On Mon, Aug 29, 2005 at 08:15:41PM -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: > > # select '1'::boolean; > bool > ------ > t > (1 row) > # select '0'::boolean; > bool > ------ > f > (1 row) > > so they do cast as expected ... but, if I try to do the ALTER, I get: > > # 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? ALTER TABLE "table" ALTER COLUMN field1 TYPE boolean USING CASE field1 WHEN 0 THEN false ELSE true END; /* or something to this effect */ HTH :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
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 tableALTER COLUMN field1 TYPE boolean USING CASE WHEN field1 = 0 THEN FALSE WHEN field1 = 1 THEN TRUE ELSE NULL END; --
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> # ALTER TABLE table ALTER COLUMN field1 type boolean;
> ERROR: column "field1" cannot be cast to type "pg_catalog.bool"
> Should this not work?
No, because there's no built-in cast from smallint to bool. You could
do something like
... type boolean using case when field1=0 then false else true end;
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > You could do something like > > ... type boolean using case when field1=0 then false else true end; Or you could save typing and just use "USING field1<>0" Odd that everyone did a CASE for that. -- greg
On Mon, 29 Aug 2005, Tom Lane wrote: > "Marc G. Fournier" <scrappy@postgresql.org> writes: >> # ALTER TABLE table ALTER COLUMN field1 type boolean; >> ERROR: column "field1" cannot be cast to type "pg_catalog.bool" > >> Should this not work? > > No, because there's no built-in cast from smallint to bool. You could > do something like > > ... type boolean using case when field1=0 then false else true end; 'k, I just took a read through the "CREATE CAST" man page, and don't think I can use that for this, but is there some way I can create a cast for this, so that we don't have to go through the complete application and change "VALUES ( 0 );" to "VALUES ( '0' );" ... Again, from reading the man page, I'm guessing not, but just want to make sure that I haven't missed anything ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> On Mon, 29 Aug 2005, Tom Lane wrote:
>> No, because there's no built-in cast from smallint to bool.
> 'k, I just took a read through the "CREATE CAST" man page, and don't think
> I can use that for this,
Sure you can. Make a SQL or PLPGSQL function that does the conversion
you want and then create a cast using it.
regards, tom lane
Tom Lane wrote: >"Marc G. Fournier" <scrappy@postgresql.org> writes: > > >>On Mon, 29 Aug 2005, Tom Lane wrote: >> >> >>>No, because there's no built-in cast from smallint to bool. >>> >>> > > > >>'k, I just took a read through the "CREATE CAST" man page, and don't think >>I can use that for this, >> >> > >Sure you can. Make a SQL or PLPGSQL function that does the conversion >you want and then create a cast using it. > > That probably won't help him much with "values(0)": andrew=# create function ibool(smallint) returns boolean language sql as $$ select $1 <> 0 $$; CREATE FUNCTION andrew=# create cast (smallint as boolean) with function ibool(smallint) as implicit; CREATE CAST andrew=# insert into foobool values(0); ERROR: column "x" is of type boolean but expression is of type integer HINT: You will need to rewrite or cast the expression. Is there a way to make the builtin int to bool cast implicit? cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> Tom Lane wrote:
>> Sure you can. Make a SQL or PLPGSQL function that does the conversion
>> you want and then create a cast using it.
> That probably won't help him much with "values(0)":
If I remember the context of the original request, it wasn't about that
anyway, but about dealing with an existing smallint column.
> Is there a way to make the builtin int to bool cast implicit?
I think you'd have to go and hack the pg_cast entry ... but that cast is
new in 8.1 anyway, so it doesn't apply to Marc's problem (yet).
If we want to make it cover that specific scenario, changing it to AS
ASSIGNMENT would be sufficient; I don't think it needs to be IMPLICIT.
(I generally find cross-type-category implicit casts to be dangerous.)
regards, tom lane
On Thu, 1 Sep 2005, Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: >> Tom Lane wrote: >>> Sure you can. Make a SQL or PLPGSQL function that does the conversion >>> you want and then create a cast using it. > >> That probably won't help him much with "values(0)": > > If I remember the context of the original request, it wasn't about that > anyway, but about dealing with an existing smallint column. Nope, actually, the original was to just convert an existing table from using smallint->boolean, but what I'm looking at with the CREATE CAST is to avoid reducing the # of changes that I have to make to the existing application, so being able to auto-cast 0->'f' on an INSERT/UPDATE would help wtih that ... The app still needs to be fixed, but this would allow for the initial change to be made a bit easier ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664