将多个布尔列连接成单个varchar列

Question

我正在使用PostgreSQL将数据从一个表转换到另一个表，并且目前在布尔列方面遇到了一些挑战。我想将具有布尔值的表转换为单个分号分隔的varchar字段。

以下是我正在执行的两个CREATE TABLE语句。

CREATE TABLE Color_Table
(
"Id" VARCHAR(255),
"IsRed" BOOL,
"IsOrange" BOOL,
"IsYellow" BOOL,
"IsGreen" BOOL,
"IsBlue" BOOL,
"IsIndigo" BOOL,
"IsViolet" BOOL
)

CREATE TABLE Concatenated_Table
(
"Id" VARCHAR(255),
"Semicolon_Colors" VARCHAR(255)
)

这是我要插入Color_Table中的一行示例。我的期望是将其转换为：红色; 绿色; 靛蓝色; 紫罗兰色;

INSERT INTO Color_Table
(
"Id",
"IsRed",
"IsOrange",
"IsYellow",
"IsGreen",
"IsBlue",
"IsIndigo",
"IsViolet"
) 

VALUES
(
'12345_abcd_67890',
true,
false,
false,
true,
false,
true,
true
)

我试图通过声明临时变量来创建它，但是收到了错误消息。下面是我尝试使用的脚本/方法。

DECLARE TempVariable VARCHAR(255);
INSERT INTO Concatenated_Table
    (
        "Id",
        "Semicolon_Colors"
    )
SELECT
colors."Id",
BEGIN
    TempVariable = ''

    IF(colors."IsRed" = true)
    BEGIN
        CONCAT(TempVariable, 'Red; ')
    END

    IF(colors."IsOrange" = true)
    BEGIN
        CONCAT(TempVariable, 'Orange; ')
    END

    IF(colors."IsYellow" = true)
    BEGIN
        CONCAT(TempVariable, 'Yellow; ')
    END

    IF(colors."IsGreen" = true)
    BEGIN
        CONCAT(TempVariable, 'Green; ')
    END

    IF(colors."IsBlue" = true)
    BEGIN
        CONCAT(TempVariable, 'Blue; ')
    END

    IF(colors."IsIndigo" = true)
    BEGIN
        CONCAT(TempVariable, 'Indigo; ')
    END

    IF(colors."IsViolet" = true)
    BEGIN
        CONCAT(TempVariable, 'Violet; ')
    END
END
FROM Color_Table colors

非常感谢您对如何实现这一点提供任何建议！

- Roger Mitchell

---

但问题是针对PostgreSQL还是其他标签...？ - ScaisEdge

最好使用PostgreSQL，但如果必要的话也可以在MS SQL Server中完成。为了避免将其与现有数据库分离，更愿意在PostgreSQL中处理。 - Roger Mitchell

通常情况下，想要将多个值存储在单个列中是一个不好的想法。你如何查询所有蓝色和黄色等的行？无论如何，更传统的方法可能是使用整数列，在其中应用位比较以确定哪些颜色是开启或关闭的。 - snow_FFFFFF

@scaisEdge 修改了，谢谢您的建议。 - Roger Mitchell

@snow_FFFFFF 两个表都存在，但需要将数据连接到单个列中。 - Roger Mitchell

更好的设计是使用一个代理表，其中包含“Id”和“Color”字段，该表将包含给定ID的所有有效颜色。这样可以轻松查询，并允许在将来添加其他颜色。 - D Stanley

Answer 1

使用concat_ws()可以简化连接操作，因为它会处理分隔符;并忽略空值，这意味着case表达式不需要显式的else ''部分：

insert into Concatenated_Table ("Id", "Semicolon_Colors")
select "Id", 
       concat_ws('; ', 
          case when "IsRed" then 'Red' end, 
          case when "IsOrange" then 'Red' end, 
          case when "IsYellow" then 'Yellow' end ,
          case when "IsGreen" then 'Green' end,
          case when "IsBlue" then 'Blue' end,
          case when "IsIndigo" then 'Indigo' end,
          case when "IsViolet" then 'Violet' end) 
from Color_Table;

如果确实需要保留各个颜色列，我宁愿使用上述select语句创建视图，而不是将数据复制到新表中。concat和case语句非常便宜。出于性能原因，没有必要复制数据。

---

无关紧要，但是：您应该尽量避免使用带引号的标识符"IsRead"，它们会带来比它们值得的麻烦更多的麻烦。(但是，如果您使用它们，您应该保持一致，您的表名不使用带引号的标识符，但是您的列使用带引号的标识符)。

- user330315

---

谢谢您的回复！我尝试了这个和@snow_FFFFFF，两者都有效。我也感谢您关于规范的评论，这是我第一次涉足Postgres。我的用例是展示源表与目标表，以便在最终推入另一个数据库之前修改目标表。虽然这有点牵强，但我觉得这种方法对于源和目标之间所需的翻译更加舒适。 - Roger Mitchell

Answer 2

我仍然认为表格设计不好，但是你应该可以将所有情况连接到单个列中 - 类似于这样

更新: 从mssql更改为Postgres语法:

SELECT
"Id",
CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(
  CASE "IsRed" WHEN true THEN 'Red; ' ELSE '' END,
  CASE "IsOrange" WHEN true THEN 'Red; ' ELSE '' END), 
  CASE "IsYellow" WHEN true THEN 'Yellow; ' ELSE '' END),
  CASE "IsGreen" WHEN true THEN 'Green; ' ELSE '' END),
  CASE "IsBlue" WHEN true THEN 'Blue; ' ELSE '' END),
  CASE "IsIndigo" WHEN true THEN 'Indigo; ' ELSE '' END),
  CASE "IsViolet" WHEN true THEN 'Violet; ' ELSE '' END)
  AS Semicolon_Colors
FROM color_table

另一种更简洁的语法（感谢 @a_horse_with_no_name）：

SELECT
"Id",    
  CASE WHEN "IsRed" THEN 'Red; ' ELSE '' END ||
  CASE WHEN "IsOrange" THEN 'Red; ' ELSE '' END || 
  CASE WHEN "IsYellow" THEN 'Yellow; ' ELSE '' END ||
  CASE WHEN "IsGreen" THEN 'Green; ' ELSE '' END ||
  CASE WHEN "IsBlue" THEN 'Blue; ' ELSE '' END ||
  CASE WHEN "IsIndigo" THEN 'Indigo; ' ELSE '' END ||
  CASE WHEN "IsViolet" THEN 'Violet; ' ELSE '' END
  AS Semicolon_Colors
FROM color_table

- snow_FFFFFF

---

SQL（和Postgres）使用||来连接字符串，而不是+，并且可以简化为case when isread then 'Red; ' else '' end。 - user330315

@a_horse_with_no_name - 谢谢 - 我只是在更新Postgre的内容 - 我不知道||运算符 - 这将使它更加简洁。 - snow_FFFFFF

对于Postgres来说，嵌套concat()调用并不是必需的。你可以使用concat(case .. end, case ... end, case .. end, ...)。 - user330315

感谢@snow_FFFFFF发布这篇文章，非常感谢您在此事上的帮助。我知道这不是最佳方案，但它是有意为之的，因为源表具有多个布尔列，并且目标表是另一个数据库集成的阶段，架构师不希望存储多个布尔属性。 - Roger Mitchell