Stack Exchange
POSTGRES - change value of parameters to oposite
I've got table with parameters (varchar) and some of parameters should be equal only 1 or 0. I want to change this parameters to opposite without knowing of old value.
In Mysql I used trick
UPDATE `parameters` SET `val`=(val^1) WHERE `name` = xxx

but how to do this in postgres?

Use - if the parameter is a number:
update parameters
    set val = 1 - val
    where name = xxx;

Use case logic if the parameter is a string:
update parameters
    set val = (case when val = '0' then '1' else '0' end)
    where name = xxx;

This guarantees that the result is 0/1, even if -- for some reason -- the existing value is different.

SQL Error [42883]: integer - character varying

val = 1 - NULLIF(val, '')::int is the solution

@WuBe . . . Use the case expression.  ^ is a bitwise XOR, suggesting that the type in MySQL is not a character (based on your description of how the code works).

if parameters is an integer subtract 1 and then apply the absolute function
ie
update parameters set val = abs(val - 1) where name = xxx
any 1 will become = 0,  any 0 becomes = 1

Solution is:
UPDATE parameters SET val = 1 - NULLIF(val, '')::int WHERE


or even better val = 1 - val::int
