PostgreSQL array_agg returns null if first param is null

Question

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?

user330315 · Accepted Answer · 2021-11-24 11:01:43Z

2

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')

answered Nov 24, 2021 at 11:01

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Ah cool thanks! thats what i need.