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 ?

This should work for text columns. What are the types of the columns? — klin
– klin, Commented May 22, 2019 at 18:10
concat_ws() could simplify this string expression. postgresql.org/docs/current/functions-string.html — wildplasser
– wildplasser, Commented May 22, 2019 at 18:13
concat() was introduced in Postgres 9.1, probably you use an older version. Upgrade. — klin
– klin, Commented May 22, 2019 at 18:27

Arulkumar · Accepted Answer · 2019-05-22 19:05:28Z

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;

edited May 22, 2019 at 19:05

answered May 22, 2019 at 18:10

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user8545255 Over a year ago

column data types are text . Even i tried above query defining data types explicitly .But it gives me the same error.

Arulkumar Over a year ago

@user8545255

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

is this working

Jeremy Over a year ago

Just be aware that || will return null if any of the strings to be concatenated are null. The concat function does not do that.

pensnarik · Accepted Answer · 2020-02-11 10:23:35Z

0

You can also consider using format function:

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

answered Feb 11, 2020 at 10:23

pensnarik

1,2723 gold badges12 silver badges16 bronze badges

Collectives™ on Stack Overflow

Concat function in postgresql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related