PostgreSQL - function concat_ws(unknown, character varying, character varying) does not exist

Question

I have the following sql:

SELECT concat_ws(', ', address1, address2, address3) as address
FROM mydb."addresses"

But I get the following error:

function concat_ws(unknown, character varying, character varying, character varying) does not exist

I am using PostgreSQL 9.0

Answer 1

concat_ws is available from version 9.1.

I strongly recommend to update your database.

---

demo: db<>fiddle

Version for 9.0:

SELECT array_to_string(array_agg(field), ', ')    -- 4
FROM (
    SELECT 
        unnest(                                   -- 2
            ARRAY[address1, address2, address3]   -- 1
        ) as field
    FROM address
) s
WHERE field is NOT NULL                           -- 3

1. Put all columns together into one array
2. unnest() this array. This expands the element into each row
3. filter the NULL values (that's what concat_ws() does)
4. Reaggregate and transform into the string