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?

Lakenyalaker

Answer 1

CREATE OR REPLACE FUNCTION boolean1(i smallint) RETURNS boolean AS $$
    BEGIN
            RETURN (i::smallint)::int::bool;
    END;
$$ LANGUAGE plpgsql;

CREATE CAST (smallint AS boolean) WITH FUNCTION boolean1(smallint) AS ASSIGNMENT;

Glabrescent

Answer 2

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;

Lipps

Answer 3

Another option is to cast your integer to text, then cast as bool.

It's not an intuitive way to do it, but it works.

---

In postgresql, you can:

result_indicateur = var integer ,
value = 0 or 1

cast(result_indicateur::text as boolean)

result_indicateur|
-----------------+
true             |
----

---

Valid literal values for the "true" state are:
'1'

For the "false" state, the following values can be used:
'0'

Source: datatype-boolean

Uncurl

This is like the int way in the question, but worse. – Definitive