Replace Oracle sql with postgres sql-CodePudding

how to replace bitand function and decode oracle function in postgres sql?

select decode(bitand(p.privilege, 2), 2, 'true', 'false') as is_approver 
from person p;

tried below sql

select case ((p.privilege & 2 = 2))::int when 2 then 'true' else 'false' end as is_approver  
from person p;

CodePudding user response：

The expression p.privilege & 2 = 2 will return a boolean value. If you can work with them inside your application, then all you need is:

select (p.privilege::int & 2) = 2 as is_approver
from person p;

If you really want string values for true and false, then you need to test the boolean value. Your expression is comparing the boolean value to an integer

select case 
         when (p.privilege::int & 2) = 2 then 'true'
         else 'false'
       end as is_approver
from person p;

CodePudding user response：

That should be

select (p.privilege::bigint & 2) = 2 as is_approver 
from person p;