sql - 直接在列中添加换行符或回车符到 Postgres 中的分隔符值

Question

我有以下查询:

SELECT id,
concat_ws(', ',
            case when isBlue then 'Blue' end,
            case when isMale then 'Male' end,
            case when isAdult then 'Adult' end) as Person1,
concat_ws(', ',
            case when isBrown then 'Brown' end,
            case when isFemale then 'Female' end,
            case when isAdult then 'Adult' end) as Person2          
from misc_table  
where id <> NULL 
order by id

输出如下

| id | Person1             | Person2
----------------------------------------------
| 1  | Blue, Male, Adult   | Brown, Female, Adult
----------------------------------------------
| 2  | Blue, Male, Adult   | Brown, Female, Adult  

但是，我宁愿让它显示为:

| id | Person1             | Person2
----------------------------------------------
| 1  | Blue,               | Brown,
|    | Male,               | Female,
|    | Adult               | Adult 
----------------------------------------------
| 2  | Blue,               | Brown,
|    | Male,               | Female,
|    | Adult               | Adult 

似乎找不到实现此目的的简单方法。任何建议表示赞赏!

Answer 1

如果使用 E''，则可以在字符串文字中使用一些 C 风格的转义符字符串，来自 fine manual :

4.1.2.2. String Constants with C-style Escapes

PostgreSQL also accepts "escape" string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g., E'foo'. (When continuing an escape string constant across lines, write E only before the first opening quote.) Within an escape string, a backslash character (\) begins a C-like backslash escape sequence, in which the combination of backslash and following character(s) represent a special byte value, as shown in Table 4-1.

所以你可以说:

SELECT id,
concat_ws(E',\n', ...
-- -------^^^^^^

那会给你一些 +签到 psql虽然输出:

| id | Person1             | Person2
----------------------------------------------
| 1  | Blue,              +| Brown,          +
|    | Male,              +| Female,         +
|    | Adult               | Adult 
...

但这只是psql告诉你有一个多行列值。

顺便说一句，id <> null没有做你可能认为的事情，你几乎肯定想说 id is not null以获得合理的结果。