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?

Ispirer SQLWays Migrations · Accepted Answer · 2015-07-10 15:03:32Z

21

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;

answered Jul 10, 2015 at 15:03

Ispirer SQLWays Migrations

1,0589 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mowwwalker · Accepted Answer · 2020-10-11 22:35:24Z

16

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;

answered Oct 11, 2020 at 22:35

mowwwalker

17.5k30 gold badges109 silver badges166 bronze badges

Comments

tdy · Accepted Answer · 2024-06-12 23:12:07Z

-1

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

edited Jun 12, 2024 at 23:12

tdy

42.1k42 gold badges124 silver badges125 bronze badges

answered Jun 12, 2024 at 11:42

pierre_ld

12 silver badges4 bronze badges

1 Comment

Ry- Over a year ago

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

Collectives™ on Stack Overflow

Casting smallint to boolean in PostgreSQL

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related