CODEWITHSUNDEEP
sqlpostgresqlhivecoalesce
user8545255
user8545255

Reputation: 839

Concat function in postgresql

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 ?

Upvotes: 1

Views: 1842

Answers (2)

pensnarik
pensnarik

Reputation: 1252

You can also consider using format function:

SELECT coalesce(product_name, format('%s - %s - UNKNOWN', trim(product_id), trim(plan_code)))

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13237

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;

Upvotes: 1

Related Questions