4

In Postgres I am trying to included a comma separated list of linked ip addresses to a particular server in the result rows of server data.

Initialially I used the FILTER functionality which seemed ideal for my purposes

STRING_AGG(ipadd, ',') FILTER ( WHERE ipadd is NULL OR ipadd != '') AS ipaddresses

Producing results lines such as

  server1;data;etc; 1.2.3.4;
  server2;data;etc; 11.22.33.44, 22.33.44.55;
  server3;data;etc;
  server3;data;etc; 23.45.67.89

However, it has come to light that the server this will be running on uses a lower version postgresql which does not support the FILTER functionality.

Due to this I needed to simplify the call to 

STRING_AGG(ipadd, ',') AS ipaddresses

Which due to the existence of potentially empty or null entries in the multiple rows returned from the second table, returns something like the following:

  server1;data;etc; ,1.2.3.4,,,;
  server2;data;etc; 11.22.33.44,,, 22.33.44.55,,,;
  server3;data;etc; ,,,,,,,,,
  server3;data;etc; ,,23.45.67.89,,

Each empty or null entry produces another empty field which is included with the correct data.

Is there a pre-FILTER way of achieving the same results as the FILTER query above, or even just removing all instances of commas that aren't separating two returned pieces of data.

At present I'm just parsing these out on the server side after the call, but I would prefer to do it on the SQL side of things if possible.

JamesA
  • 365
  • 3
  • 11

2 Answers2

12

string_agg() will ignore null values anyway so the filter on is null isn't really necessary.

The filter condition ipadd <> '' can be replaced by simply "converting" an empty string to a null value (which in turn will then be ignored by the aggregate function)

string_agg(nullif(ipadd,''), ',')
4

I would replace the filter with case:

STRING_AGG(CASE WHEN ipadd is NULL or ipadd <> '' THEN ipadd END, ','
          )  AS ipaddresses
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786