Re: BUG #1135: integer::bit returns incorrect results

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> These all return 0.
>    select 1::bit;
>    select 2::bit;
>    select 111::bit;
>    select 101::bit;

"bit" means "bit(1)" per SQL spec, so only the first of these could
possibly act as you're expecting anyway.

The reason none of them do is that the coercion function actually yields
bit(32), with the MSB of the integer at the left, and then coercion to
bit(1) drops all but the sign bit.  So for example

regression=# select (255)::bit;
 bit
-----
 0
(1 row)

regression=# select (255)::bit(32);
               bit
----------------------------------
 00000000000000000000000011111111
(1 row)

regression=# select (-255)::bit(32);
               bit
----------------------------------
 11111111111111111111111100000001
(1 row)

regression=# select (-255)::bit(1);
 bit
-----
 1
(1 row)


There was previous discussion of this a month or so ago, but nobody
could come up with a reasonable definition that didn't have large
implementability or backwards-compatibility issues ...

            regards, tom lane