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

0
votes

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'

2
postgresqlviews

2 Answers

3
votes

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,..
0
votes

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;