根据Postgres中的另一列设置列的值?

I'm working with Postgres 9.1 and I have added a new boolean column to my table:

我正在使用postgres9.1，并在我的表中添加了一个新的布尔列:

ALTER TABLE frontend_item ADD COLUMN is_generic BOOLEAN;

The value of this column will be based on the value of another column, code. If characters 10-11 of code are AA then the value of is_generic should be TRUE. Otherwise it should be false (or null if it hasn't been set yet).

这个列的值将基于另一个列代码的值。如果代码的字符10-11是AA，那么is_generic的值应该为TRUE。否则它应该是false(或null，如果还没有设置)。

My question, is how can I do this in Postgres? I've been able to work out some individual components using the docs:

我的问题是，如何在Postgres中做到这一点?我已经能用文档算出了一些单独的组件:

 UPDATE frontend_item SET is_generic...

And then I know I can get the substring of code as follows:

然后我知道我可以得到如下代码的子字符串:

 substring(code from 10 for 2)

But how do I turn substring into a boolean, and then glue it together with the UPDATE statement?

但是，如何将子字符串转换为一个布尔值，然后将其与UPDATE语句结合在一起呢?

1 个解决方案

#1

UPDATE frontend_item
SET    is_generic = (substring(code from 10 for 2) = 'AA');

But do you really need the redundant column? You can just keep using the expression substring(code from 10 for 2), which is more reliable in the face of possible updates to the table. The cost for the function is low and keeping the table small is a benefit for overall performance.

但你真的需要冗余列吗?您可以继续使用表达式子字符串(从10到2的代码)，在面对可能的更新时更可靠。该函数的成本很低，并且保持表的大小对整体性能是有利的。

Redundant storage is rarely a good idea. Only for special optimizations.

冗余存储很少是个好主意。只对特别的优化。

BTW, there is a less verbose Postgres variant doing the same:

顺便说一句，有一种不那么冗长的Postgres变体也做同样的事情:

substr(code, 10, 2)

See string functions in the manual.

参见手册中的字符串函数。

#1

UPDATE frontend_item
SET    is_generic = (substring(code from 10 for 2) = 'AA');