Skip to content

PostgreSQL Compatibility: boolean casting #13419

@michae2

Description

@michae2

In bool.html we mention that boolean values can be cast to numeric types. I think we should also mention in postgresql-compatibility.html#features-that-differ-from-postgresql that this is an extension of what PostgreSQL allows (they only allow casting between BOOL and INTEGER / INT / INT4, not any of the other numeric types).

Here's a demonstration. PostgreSQL 14.2:

michae2=# SELECT true::INT2;
2022-03-29 15:50:58.483 PDT [59188] ERROR:  cannot cast type boolean to smallint at character 12
2022-03-29 15:50:58.483 PDT [59188] STATEMENT:  SELECT true::INT2;
ERROR:  cannot cast type boolean to smallint
LINE 1: SELECT true::INT2;
                   ^

michae2=# SELECT true::INT4;
 int4
------
    1
(1 row)

michae2=# SELECT true::INT8;
2022-03-29 15:51:04.366 PDT [59188] ERROR:  cannot cast type boolean to bigint at character 12
2022-03-29 15:51:04.366 PDT [59188] STATEMENT:  SELECT true::INT8;
ERROR:  cannot cast type boolean to bigint
LINE 1: SELECT true::INT8;
                   ^

michae2=# SELECT true::FLOAT4;
2022-03-29 15:51:08.538 PDT [59188] ERROR:  cannot cast type boolean to real at character 12
2022-03-29 15:51:08.538 PDT [59188] STATEMENT:  SELECT true::FLOAT4;
ERROR:  cannot cast type boolean to real
LINE 1: SELECT true::FLOAT4;
                   ^

michae2=# SELECT true::FLOAT8;
2022-03-29 15:51:11.104 PDT [59188] ERROR:  cannot cast type boolean to double precision at character 12
2022-03-29 15:51:11.104 PDT [59188] STATEMENT:  SELECT true::FLOAT8;
ERROR:  cannot cast type boolean to double precision
LINE 1: SELECT true::FLOAT8;
                   ^

michae2=# SELECT true::DECIMAL;
2022-03-29 15:51:15.567 PDT [59188] ERROR:  cannot cast type boolean to numeric at character 12
2022-03-29 15:51:15.567 PDT [59188] STATEMENT:  SELECT true::DECIMAL;
ERROR:  cannot cast type boolean to numeric
LINE 1: SELECT true::DECIMAL;
                   ^

CockroachDB 22.1:

[email protected]:26257/defaultdb> SELECT true::INT2;
  int2
--------
     1
(1 row)


Time: 0ms total (execution 0ms / network 0ms)

[email protected]:26257/defaultdb> SELECT true::INT4;
  int4
--------
     1
(1 row)


Time: 1ms total (execution 0ms / network 0ms)

[email protected]:26257/defaultdb> SELECT true::INT8;
  int8
--------
     1
(1 row)


Time: 0ms total (execution 0ms / network 0ms)

[email protected]:26257/defaultdb> SELECT true::FLOAT4;
  float4
----------
       1
(1 row)


Time: 1ms total (execution 0ms / network 0ms)

[email protected]:26257/defaultdb> SELECT true::FLOAT8;
  float8
----------
       1
(1 row)


Time: 1ms total (execution 0ms / network 0ms)

[email protected]:26257/defaultdb> SELECT true::DECIMAL;
  numeric
-----------
        1
(1 row)


Time: 1ms total (execution 0ms / network 0ms)

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions