Postgres 9.1's concat_ws equivalent in Amazon redshift

user_3186884 0
Viewed 1746

I've got a query originally written for pg9.1 that I'm trying to fix for use on redshift as follows

select concat_ws(' | ', p.gb_id, p.aro_id, p.gb_name) c from (
select ca.p_id,
    avg(ca.ab) as ab
    from public.fca
        join temp_s_ids s on ca.s_id = s.s_id
    group by ca.p_id
) as x
    join public.dim_protein as p on x.protein_id = p.protein_id;";

I've been trying to test it out on my own, but as it is created from temporary tables that are created by a php session, I haven't had any luck yet. However, my guess is that the concat_ws function isn't working as expected in redshift.

amazon-redshift concat sql
2 Answers

I don't believe there is an equivalent in redshift. You will have to roll your own. If there are no NULLS you can just use the concatenation operator ||:

 SELECT p.gb_id || ' | ' || p.aro_id || ' | ' || p.gb_name c
 FROM...

If you have to worry about nulls (and its separator):

 SELECT CASE WHEN p.gb_id IS NOT NULL THEN p.gb_id || ' | ' END || CASE WHEN p.aro_id IS NOT NULL THEN p.aro_id || ' | ' END || COALESCE(p.gb_name, '') c
 FROM

Perhaps that can be simplified, but I believe it will do the trick.

user_22210010

To handle NULLs, you can do:

select trim('|' from
            coalesce('|' || p.gb_id) ||
            coalesce('|' || p.p.aro_id) ||
            coalesce('|' || p.gb_name)
           )
from . . .
user_11440350
Related