How to coalesce an empty/null value that is part of Concact string in Postgres?

Question

I have the following CREATE View statement where I am concatenating a couple fields. It grabs the 1st 100 characters of the first field, then appends an integer field

  CREATE OR REPLACE VIEW $"schema_1"."tbl_225_customid" AS 
  SELECT tbl_225.id,
  ("substring"((field_2)::text, 0, 100)::text) ||  ' (' || "field_1" || ')' as fullname
  FROM schema_1.tbl_225;

This works fine when there is data in field_2, however, when field_2 is null, the view shows NULL instead of just the integer part of the expression.

I tried wrapping this in a coalesce statement like this, but it throws an error.

   CREATE OR REPLACE VIEW $"schema_1"."tbl_225_customid" AS 
   SELECT tbl_225.id,
   COALESCE(("substring"((field_2)::text, 0, 100)::text),'') ||  ' (' || "field_1" || ')' as fullname
   FROM schema_1.tbl_225;

How do I write the concat string such that, if field_2 is null, it will use '' for that and still result in value?

Sample data and desired results would be helpful.

Gordon Linoff
– Gordon Linoff

2020-05-04 19:54:27 +00:00
Commented May 4, 2020 at 19:54 — Gordon Linoff
– Gordon Linoff, Commented May 4, 2020 at 19:54

GMB · Accepted Answer · 2020-05-04 21:31:01Z

1

I would recommend simply using string function concat_ws() for this: it ignores null value by design, so you just don't need to worry about them:

select 
    tbl_225.id,
    concat_ws('', substring(field_2::text, 0, 100), ' (' || field_1 || ')') as fullname
from schema_1.tbl_225;

answered May 4, 2020 at 21:31

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2020-05-04 19:54:31Z

0

Use coalesce():

SELECT tbl_225.id,
        COALESCE(substring(field_2::text, 0, 100) , '') ||  coalesce(' (' || "field_1" || ')', '') as fullname
FROM schema_1.tbl_225;

answered May 4, 2020 at 19:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to coalesce an empty/null value that is part of Concact string in Postgres?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related