如何在一个查询中切换Postgres中的布尔值 [英] How to toggle a boolean in postgres in one query

问题描述

我正在尝试更新postgres表中的一行。我想切换一个布尔字段。

I'm trying to update a row in a postgres table. I want to toggle a boolean field.

我想知道是否有单个查询来更新该字段，而不是先检查值是什么，然后相反地更新字段。

Instead of first checking what the value is and updating the field with the opposite, I was wondering if there was a single query to update the field.

我找到了MySQL的解决方案，但不适用于postgres：

I found a solution for MySQL, but its not working for postgres:

 UPDATE table SET boolean_field = !boolean_field WHERE id = :id

 Error: Operator does not exist. ! boolean

postgres中是否有类似的语法？

Is there an similar syntax in postgres?

推荐答案

使用不要：

UPDATE table SET boolean_field = NOT boolean_field WHERE id = :id

当旧值等于FALSE时，它变成TRUE，反之亦然。 NULL字段不会翻转，没有内容可翻转。

When the old value equals FALSE then it turns into TRUE and visa versa. A NULL field won't flip, there is nothing to flip to.

完整示例：

CREATE TABLE test(id serial, boolean_field boolean);

INSERT INTO test(boolean_field) 
VALUES(null),(false), (true) 
RETURNING *;

并运行测试：

UPDATE test
SET boolean_field = NOT boolean_field 
RETURNING *;

这篇关于如何在一个查询中切换Postgres中的布尔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！