我可以将PostgreSQL中的一组布尔列转换为单个位图吗？

Question

我想将以下查询进行转换：

SELECT BoolA, BoolB, BoolC, BoolD FROM MyTable;

将各值定义为位，生成一个位掩码。

例如，如果BoolA和BoolD都为true，则输出1001或者9。

我想实现的大致思路如下：

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;

但我不确定这是否是最佳方法，而且似乎有些啰嗦。有没有简单的方法可以做到这一点？

- Mike Christensen

Answer 1

对于位掩码，类型bitstring将是更好的选择。可能看起来像这样：

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

true 转换为 1，false 转换为 0。您可以简单地将位连接成位字符串。

将 bit(n) 转换为整数

看起来您需要一个 整数 作为结果 - 这有一个简单且快速的方法：

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

确保阅读手册中第 "位串函数和运算符" 章节的细则。

---

我想到了另外两个点子，并且快速地整理了一个包含1万行的测试/参考文件，以总结所有内容。

测试设置：

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;

你也可以使用位或运算符|代替加法运算符+。
单独的测试运行显示五种方法基本上具有相同的性能。

- Erwin Brandstetter

---

这种方法可能可行，但据我所知，Npgsql驱动程序会将bit varying类型转换为布尔数组，这不是我想要的。虽然我需要尝试一下才能确认，因为我找不到任何文档记录它。 - Mike Christensen

刚刚确认 - Npgsql 将把 bit varying 转换为一个字符串，看起来像 "1001" - 因此，尽管这种方法在某些情况下可能是有效的，但并不完全符合我的要求。 - Mike Christensen

不，我并没有“声称”，但是当我提出建议时，我确实在考虑MySQL。因此，我的最初假设实际上是正确的。但我不确定，在Mike报告这个想法不起作用之后，我开始思考true在PostgreSQL中可能会转换为-1，而不是像在MySQL中一样转换为1。后来证明是错误的，所以我最终又确认了我的最初猜测。另外，如果您将整个表达式转换为int，我认为您的想法对Mike可能有效。 - Andriy M

@MikeChristensen：请查看我修改后的答案，提供了一个整数解决方案。 - Erwin Brandstetter

@AndriyM：好的，我已经重新表达了我的回答。此外，你对于适合Mike需求的解决方案走在了正确的轨道上。 - Erwin Brandstetter

Answer 2

也许可以这样做：

SELECT
  (CASE WHEN BoolA THEN 1 ELSE 0 END << 0) +
  (CASE WHEN BoolB THEN 1 ELSE 0 END << 1) +
  (CASE WHEN BoolC THEN 1 ELSE 0 END << 2) +
  (CASE WHEN BoolD THEN 1 ELSE 0 END << 3) AS BitMask
FROM MyTable;

其中 << 是按位左移运算符。

- Andriy M

---

这会导致错误 operator does not exist: boolean << integer。 - Mike Christensen

@MikeChristensen：哦，我明白了。我以为在PostgreSQL中布尔值会被隐式转换为整数。我太自以为是了，抱歉。我编辑了我的答案，现在与你的差别不大。 - Andriy M

啊，我搞定了。那只是一个操作顺序的问题，我需要加上括号 :) - Mike Christensen

@MikeChristensen：我认为你应该发布自己的答案并接受它！ - Andriy M

你可能是对的，但我会给你一个赞成位移想法的赞 :) 我认为使它更简洁的唯一方法是将其包装在一个函数中。 - Mike Christensen

Answer 3

我也想到了这种方法。除非有更聪明的方法，否则这是我能找到的最简洁的方法，而不需要编写自定义函数。我将接受这个答案。

SELECT
  (BoolD::int << 0) +
  (BoolC::int << 1) +
  (BoolB::int << 2) +
  (BoolA::int << 3)
from MyTable;

- Mike Christensen

---

我认为这是目前最好的解决方案。所以你是对的，如果有人提出更好的方案，那肯定值得接受。 - Andriy M