Concat function in postgresql

Question

I have the below select statement in hive .It executes perfectely fine.

In Hive

select
COALESCE(product_name,CONCAT(CONCAT(CONCAT(TRIM(product_id),' - 
'),trim(plan_code)),' - UNKNOWN')) as product_name
from table name;

I am trying to use the same select statement in POSTGRESQL and it throw me error saying "

Query execution failed

Reason:

SQL Error [42883]: ERROR: function concat(text, unknown) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

In postgresql:

select
COALESCE(product_name,CONCAT(CONCAT(CONCAT(TRIM(product_id),' - 
'),trim(plan_code)),' - UNKNOWN')) as product_name
from table name;

Could some one throw some light on this ?

Answer 1

Instead of concat try with ||:

SELECT COALESCE(product_name, 
        (TRIM(product_id) || ' - ' || TRIM(plan_code) || ' - UNKNOWN')
       ) AS product_name 
FROM tablename;

or simply a single CONCAT as:

SELECT COALESCE(product_name, 
         CONCAT(TRIM(product_id)::text, ' - ', TRIM(plan_code)::text, ' - UNKNOWN') 
       ) AS product_name
FROM tablename;