0

I have to change the value of a determined column in postgre but I never worked before with maskbits, (it's a legacy code). The current value is 1 but it's stored as a integer maskbit so if I try to do

    update table set field=0 where ...

It doesn't work and it keeps returning me 1. So I read the documentation and tried to do a AND:

    update table set field=field&0 where ...

But it doesn't worked too. Then I tried to cover all the 30 bits, without success too:

    update table set field=field&0000000000000000000000000000000 where ...

Can someone please show me how to properly change the value of a integer maskbit on postgresql?

EDIT: I found this case here in StackOverflow 

    UPDATE users SET permission = permission & ~16

which seen's to be the closest to mine so I tried to do

    UPDATE table SET field = field & ~1

because that's the only bit I have to deactivate but it still active and retuning 1 when I do a SELECT

Community
  • 1
  • 1
Ra.cavalieri
  • 143
  • 2
  • 11

1 Answers1

0

PostgreSQL may be pickier than you're used to when it comes to combining numbers of different types. You can't just use a 0, you need to use a bitstring constant of the right length, or explicitly cast an integer to the proper bit(n) type.

Bitstring constants are entered like B'00101', which would have type bit(5). Some examples you can type into psql:

select 5;
select (5::bit(5));
select (30::bit(5));
select ~(1::bit(5));
select ~B'00001';
select (B'00101' & B'11110');
select (B'00101' & ~(1::bit(5)));
NovaDenizen
  • 5,089
  • 14
  • 28
  • I've edited with a try I did. Using 'UPDATE table SET field = field & ~1' still's returning me 1, and if I try to use it as a Bit String, postgre returns errors about casting in the Update query. – Ra.cavalieri Jul 19 '16 at 12:13
  • You need to cast your `1` to the correct bitstring type, like `~(1::bit(5))`, except the `5` will probably need to be changed.. Type `\d tablename` in psql to see the proper type for your field. – NovaDenizen Jul 19 '16 at 12:50
  • Well it's a integer so I tried 'update table set field=field & ~(1::bit(32)) where ...' But received the error "operator does not exist: integer & bit" So I tried 'update table set field=field & ~(1::bit(32))::integer where ...' Which returned 'Query returned successfully: one row affected, 166 ms execution time.' But field keeps returning 1 when I SELECT – Ra.cavalieri Jul 19 '16 at 13:04
  • If the field is of type integer, then just `set field=0` or `set field = field & (~1)` should work. Maybe something else is changing it back to 1 right after you clear it. – NovaDenizen Jul 19 '16 at 13:59
  • Forgive my mistake, yes there was another property setting this field back to 1 so I changed that and the "set field = field & (~1)" solved the problem. Thanks for the help! – Ra.cavalieri Jul 19 '16 at 14:29