PostgreSQL array_agg returns null if first param is null-CodePudding

I would like to aggregate data into 1 field. So I have a query like this:

SELECT 
t.userId,
array_agg(t.column1 || ';' || t.column2) as aggregated        
FROM
mytable t
GROUP BY
t.userId

However if t.column1 is null, and t.column2 is not null, then i get a NULL returned. I would like to return something like this:

column1;column2 OR null;column2 OR column1;null

How could I do this with array_agg?

CodePudding user response：

It's not array_agg, that does that but the || operator. It yields null if any of the elements is null. To avoid that, use

concat_ws(';', t.column1, t.column2)

It essentially treats null values as empty strings.

If you want an explicit null string, you can use:

coalesce(t.column1,'null')|| ';' ||coalesce(t.column2, 'null')