我一堆布尔列转换为PostgreSQL的一个位图？ [英] Can I convert a bunch of boolean columns to a single bitmap in PostgreSQL?

问题描述

我想查询转换，如：

SELECT BoolA, BoolB, BoolC, BoolD FROM MyTable;

向一个位掩码，其中该位被上述的值所定义

Into a bitmask, where the bits are defined by the values above.

例如，如果 BoolA 和摄入是真的，我愿意 1001 或 9 。

For example, if BoolA and BoolD were true, I'd want 1001 or 9.

我有东西在头脑的效果：

I have something in mind to the effect of:

SELECT
   CASE WHEN BoolD THEN 2^0 ELSE 0 END +
   CASE WHEN BoolC THEN 2^1 ELSE 0 END +
   CASE WHEN BoolB THEN 2^2 ELSE 0 END +
   CASE WHEN BoolA THEN 2^3 ELSE 0 END
FROM MyTable;

但我不知道这是最好的方法，似乎相当冗长。有没有一种简单的方法来做到这一点？

But I'm not sure if this is the best approach and seems rather verbose. Is there an easy way to do this?

推荐答案

有关的位掩码，类型<一个href=\"http://www.postgresql.org/docs/current/interactive/datatype-bit.html\"><$c$c>bitstring会是更好的选择。可能看起来像这样那么：

For a bitmask, the type bitstring would be the better choice. Could look like this then:

SELECT BoolD::int::bit
    || BoolC::int::bit
    || BoolB::int::bit
    || BoolA::int::bit
FROM tbl;

TRUE 转换成 1 ， FALSE 到 0 。你可以简单地串联位位串。

TRUE converts to 1, FALSE to 0. You can simply concatenate bits to a bitstring.

看来你需要一个整数的结果 - 有一个简单的＆安培;快捷方式：

It seems you need an integer as result - there is a simple & fast way:

SELECT (BoolD::int::bit
     || BoolC::int::bit
     || BoolB::int::bit
     || BoolA::int::bit)::bit(4)::int
FROM tbl;

请务必阅读的章节中印刷精美的位串函数和操作符的手册。

我想出了两个想法，并用10k行组建了一个快速测试/参考总括起来。

I came up with two more ideas and put together a quick test / reference with 10k rows to sum it all up.

测试设置：

CREATE TEMP TABLE t (boola bool, boolb bool, boolc bool, boold bool);
INSERT INTO t
SELECT random()::int::bool
     , random()::int::bool
     , random()::int::bool
     , random()::int::bool
FROM   generate_series(1,10000);

演示：

SELECT  CASE WHEN boold THEN 1 ELSE 0 END
     + (CASE WHEN boolc THEN 1 ELSE 0 END << 1)
     + (CASE WHEN boolb THEN 1 ELSE 0 END << 2)
     + (CASE WHEN boola THEN 1 ELSE 0 END << 3) AS andriy

     ,  boold::int
     + (boolc::int << 1)
     + (boolb::int << 2)
     + (boola::int << 3) AS mike

     , (boola::int::bit
     || boolb::int::bit
     || boolc::int::bit
     || boold::int::bit)::bit(4)::int AS erwin1

     ,  boold::int
     | (boolc::int << 1)
     | (boolb::int << 2)
     | (boola::int << 3) AS erwin2

     , (((
       boola::int << 1)
     | boolb::int << 1)
     | boolc::int << 1)
     | boold::int        AS erwin3
FROM   t
LIMIT  15

您也可以用位或 | 而不是 + 运营结果。
个人测试运行显示基本的相同的性能所有五个方法。

You could also use a bitwise OR | instead of the + operator.
Individual test runs show basically the same performance for all five methods.

这篇关于我一堆布尔列转换为PostgreSQL的一个位图？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！