Casting smallint to boolean in PostgreSQL

Question

I am trying to cast a smallint to a boolean in PostgreSQL. This does not work out of the box, for example:

select (1::smallint)::bool;

returns "ERROR: 42846: cannot cast type smallint to boolean"

I can fix this using:

select (1::smallint)::int::bool;

but I'm wondering if there is a way I can define how to cast smallint directly to boolean?

The reason for this is that I (and others that I work with) have summary queries which cast an int column from a database table to boolean. I would like to change this column to smallint, but doing so would brake this logic because there is no direct cast from smallint to boolean. Is it possible to use the postgres CREATE CAST to define how to cast a smallint to a boolean?

Answer 1

I was trying:

ALTER TABLE mytable ALTER COLUMN mycol TYPE bool USING mycol::bool;

using the same approach as "Ispirer SQLWays Migrations"'s answer (cast smallint to int and then to boolean) worked:

ALTER TABLE mytable ALTER COLUMN mycol TYPE bool USING mycol::int::bool;