Add newline or carriage return directly in a column to separator values in Postgres

Question

I have the following query:

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

Which would output the following

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

However, I would rather have it display as:

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

Could not seem to find a simple way to achieve this. Any suggestions are appreciated!

Answer 1

You can use some C-style escapes in string literals if you use E'' strings, from the 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.

So you could say:

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

That will give you some + signs in the psql output though:

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

but that's just psql telling you that there is a multi-line column value.

BTW, id <> null doesn't do what you might think it does, you almost certainly want to say id is not null to get sensible results.

Answer 2

You can force a newline character by calling chr:

SELECT id,
concat_ws(',' || CHR(10), -- HERE
            case when isBlue then 'Blue' end,
            case when isMale then 'Male' end,
            case when isAdult then 'Adult' end) as Person1,
concat_ws(',' || CHR(10), -- And HERE
            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 IS NOT NULL -- BTW, note that nulls should be evaluated with the IS operator
order by id