How to return null value as text 'N/A' in a postgresql view that uses concat_ws

Question

I have 5 columns that I need to query using concat_ws (to make text for kml file that only has one description field and I need info from 5 columns), and in between the concatenated columns I want to add text before two columns:

SELECT concat_ws(' '::text, col1, col2, col3, 
'text_for_4:', col4, 
'text_for_5:', col5) AS concat_ws,..

This works fine, but can I create a query that will return a text of 'N/A' if a row has a null value? I.e. if col4 is null the return would be 'col1 col2 col3 text_for_4: N/A text_for_5: col5'

Politank-Z · Accepted Answer · 2015-06-02 16:29:23Z

5

Use COALESCE:

SELECT concat_ws(' '::text, COALESCE ( col1, 'N/A' ), COALESCE ( col2, 'N/A' ),
COALESCE ( col3, 'N/A' ), 
'text_for_4:', COALESCE ( col4, 'N/A' ), 
'text_for_5:', COALESCE ( col5, 'N/A' )) AS concat_ws,..

answered Jun 2, 2015 at 16:29

Politank-Z

3,7433 gold badges27 silver badges28 bronze badges

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

Comments

quarterdome · Accepted Answer · 2015-06-02 16:27:02Z

1

Use CASE statement (http://www.postgresql.org/docs/9.3/static/functions-conditional.html):

select 
  case 
    when column is null 
    then 'N/A'
    else column
  end
from table;

answered Jun 2, 2015 at 16:27

quarterdome

1,5812 gold badges12 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to return null value as text 'N/A' in a postgresql view that uses concat_ws

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