Re: 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(at)hub(dot)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(at)fetter(dot)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 table
ALTER COLUMN field1 TYPE boolean
USING CASE WHEN field1 = 0 THEN FALSE
WHEN field1 = 1 THEN TRUE
ELSE NULL
END;
--

"Marc G. Fournier" <scrappy(at)postgresql(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)postgresql(dot)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(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

"Marc G. Fournier" <scrappy(at)postgresql(dot)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

Moved off of -hackers, since its long gotten out of that realm :)

On Thu, 1 Sep 2005, Tom Lane wrote:

> "Marc G. Fournier" <scrappy(at)postgresql(dot)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.

Ah, okay, I just re-read the man page and think I stumbled upon what I
overlooked the first time ...

all I want to do is:

CREATE CAST ( 0 AS boolean )
WITH FUNCTION <I have to create this>
AS ASSIGNMENT;

And then each time I try to insert a '0' into a BOOLEAN field, it will
auto convert that (based on my function) to 'f' ...

And I'd need to do a second one for 1 -> 't' ...

Am I reading it right this time ... ?

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

Tom Lane wrote:

>"Marc G. Fournier" <scrappy(at)postgresql(dot)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

"Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
> all I want to do is:

> CREATE CAST ( 0 AS boolean )
> WITH FUNCTION <I have to create this>
> AS ASSIGNMENT;

> And then each time I try to insert a '0' into a BOOLEAN field, it will
> auto convert that (based on my function) to 'f' ...

> And I'd need to do a second one for 1 -> 't' ...

No, you want one function from smallint to boolean, and the cast the
same way. (The cast is really just syntactic sugar for invoking the
function.)

Depending on what you want this to do, you might have to make the cast
IMPLICIT rather than ASSIGNMENT. I'd try ASSIGNMENT first, though,
since it's less likely to bite you when you weren't expecting it.

regards, tom lane

Andrew Dunstan <andrew(at)dunslane(dot)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(at)dunslane(dot)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(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664